IntroductionOn this page we'll show you the basic properties of the MS SQL Connector.
Microsoft SQL Connector
Figure 1: The Connector Properties
Creating the connectorTo create the connector, leave open solutions and select:
- Tools > Settings > Connector [TAB] > Add.
- Select: "Database - Microsoft SQL"
- Click Ok
The Connector properties
|Datasource name||Textbox||This is the reference name within ETL Solutions|
|Server (instance)||Dropdown menu||Provide the name of the database server (if needed with a port: <server>, <port>)|
|Direct connection (no adhoc required)||Checkbox||When this is checked, ETL+ will not use the MS SQL OPENROWSET function, but a direct connection. Mandatory when used with AD Authentication, optional for SQL server auth. Default: Unchecked|
|Integrated||Radio button||When selected, the SQL Connection will try to use the integrated users in SQL. Note: Both the ETL+ Client and ETL SQL Server must have sufficient rights to login into this database.|
|SQL Server||Radio button||Create a normal SQL Server connection with a SQL User. Properties
|Domain User||Radio button||Login with an Active Directory account within a domain. Note: When you use a Domain User, the option Direct Connection is mandatory and selected automatically. Please take into account that the connection authentication will change from Database to Application directly (because of this, only the Test Direct button will be visible). With the AD authentication the validation is initiated (and on every run validated) directly from the Data Manager application Server, instead of the MS SQL Database server where the Data Manager Databases resides. Properties
|Database||Dropdown menu||Select the database from the connected server. The list depends on the rights from the user that is provided. If a user with sysadmin rights is provided, the list is visible, otherwise you have to provide the database name. Username and Password are required for this field to work.|
|Use NOLOCK Hint||Checkbox||When checked, a WITH (NOLOCK) statement is is send with the SELECT query. This means that the SQL Server won't wait on a lock if the server isn't busy with an UPDATE or INSERT query. Please be careful with enabling this function.|
|Handling of null values in part of key||Dropdown menu||The properties explain what they do by themselves|
|Use encrypted connections||Checkbox||
When this is checked ETL+ will make use of a SSL connection. Default: Unchecked
|Always trust server certificate||Checkbox||When this is checked ETL+ will always trust server certificates. Default: Unchecked|
- Note: When you use the following combination of options when configuring the connector the connection won't work.
- Direct connection checkbox off
- Use encrypted connections and Always trust server certificate checkboxes on
- Use of semicolon in the Password
|SQL Server||Username: sa Password: *************|
|Handling of null values in part of key||Move records with all nulls in part of key to exceptions|
You can test the connection by clicking the Test direct and Test connection buttons.