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:
-
In your preferred flat text editor, create a new text file (for example,
test.txt
in the temp
directory).
-
Make sure Windows Explorer does not hide the extension of the file (), and rename the text file by changing the
.txt
extension to .udl
(for example,
test.udl
).
-
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.
-
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.
-
Select the Connection tab, and enter the details for your
connection.
-
When satisfied, test for completeness and accuracy by clicking Test
Connection.
-
When the connection is validated, click OK to save the
details into your text file.
-
Open the UDL file in your text editor.
-
For
Oracle
or c/ values of the Import
element's Type
attribute, remove the provider value.
-
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.
2022 R1