Validating Connection Strings

There are many ways to build and validate connection strings, but here is one of the most popular.

Other than for reading data from directory services, the Business Importer uses OLE-DB connections to read data from external data sources. You can use the following procedure to build and validate an OLE-DB connection string.

To validate a connection string:

1. In your preferred flat text editor, create a new text file (for example, test.txt in the temp directory).
2. Make sure Windows Explorer does not hide the extension of the file (Tools > Folder Options > Views), and rename the text file by changing the .txt extension to .udl (for example, test.udl).
3. Depending on the architecture of your computer:
   • On a Windows 32-bit machine, double-click on the file.
   • On a 64-bit machine, open a Command Window and use the following command line (wrapped for publishing: enter all on one line):

     C:\Windows\syswow64\rundll32.exe 
          "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",
          OpenDSLFile FilePathAndUDLFileName

     For example:

     C:\Windows\syswow64\rundll32.exe 
          "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",
          OpenDSLFile C:\temp\test.udl

   The Data Link Properties dialog is displayed.
4. Ensure the Provider tab is selected, and choose the appropriate OLE-DB provider for this connection.
   
   
   

   Tip: There may be several available OLE-DB drivers to connect to an external data source, depending on which providers are installed on your computer. For instance, connection to SQL Server can be accomplished using any of the following drivers:

   • Microsoft OLE DB Provider for SQL Server
   • SQL Server Native Client 10.0
   • Microsoft OLE DB Provider for ODBC Drivers.

   Notice that in the XML file that configures this connection, the Type attribute of the Import element must be set to match the provider used. In this instance:

   Provider	Type attribute setting	Notes
   Microsoft OLE DB Provider for SQL Server	SQLServer	The provider should be omitted from the connection string.
   Microsoft OLE DB Provider for ODBC Drivers	ODBC	The provider should be included in the connection string.
   SQL Server Native Client 10.0	OLEDB	The provider should be included in the connection string.

   For more about connection strings and the Type attribute of the Import element, see ConnectionString Attribute.

5. Select the Connection tab, and enter the details for your connection.
   
   
   
6. When satisfied, test for completeness and accuracy by clicking Test Connection.
7. When the connection is validated, click OK to save the details into your text file.
8. Open the UDL file in your text editor.
   
   
   
9. For Oracle or c/ values of the Import element's Type attribute, remove the provider value.
10. Copy the [remaining] connection string from the text file, and paste it into the XML adapter file for the Business Importer.

Tip: This method does not expose all the attributes required for every kind of OLE-DB connector. For instance, extended properties for Excel or CSV files are only available in the Advanced tab of the Data Link Properties dialog. See ConnectionString Attribute for more details on the required values for each connection type.