ConnectionString Attribute

Each Import element has a ConnectionString attribute that defines an OLE-DB connection from the adapter to the external data source. However, the format of the ConnectionString attribute depends on the Type attribute of the Import element, which declares the type of data source to which the adapter connects for this particular import.

Note: The Business Importer is a 32-bit application, and 32-bit OLE-DB connection strings must be used on 64-bit operating systems.

Tip: For guidelines about building and validating a connection string, see Validating Connection Strings.

The following table shows, for each Type of data source that the import may use, the different format and attribute values for the ConnectionString.

ADSI

ADSI is used for directory data import, such as from Active Directory or eDirectory. Acceptable formats for ConnectionString when using this data source are:

• If the Business Importer is currently running as an account known to the domain, the ConnectionString may be left empty (in which case, the current account credentials are used)
• When authentication needs to be specified, use

  Login=AcctName;Password=AcctPwd

CSV

This import Type may be used both for files with delimited values (as in a true comma-separate value file), or for text files with fixed-length values.

• For delimited columns, the format is:

  Provider=Microsoft.Jet.OLEDB.4.0; Data Source=FilePathOnly; 
  Extended Properties='text;HDR=Yes;FMT=Delimited'

  where
  • FilePathOnly contains the path to the text file, but not the file name. (The file name is defined in the Query attribute of the Import element.)
  • HDR defines whether the first row (paragraph) of the file contains field/column names. When HDR=Yes, the first row is interpreted as the column names. When HDR=No, the field names are automatically assigned as F1, F2, ... Fn.
  • FMT sets the delimiter. Valid values are:
    • Delimited (default) uses the Format setting taken from the following registry key:
      • For a 32-bit operating system:

        HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

      • For a 64-bit operating system:

        HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text

      Format may have any of the following values (same values for the connection string or in the registry).
    • Delimited(;) for values separated by semi-colons (;). Alternatively, any other character except the double quotation mark can be specified in the parentheses, including a space. This custom character becomes the separator.
    • CSVDelimited for standard comma-separated values files.
    • TabDelimited for values separated by the tab character.
• For fixed length columns, the format is:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePathOnly; 
  Extended Properties='text;HDR=Yes;FMT=Fixed' 

In all cases, to specify the properties of each column in the imported file, use a schema.ini file (see Editing a Schema.ini File).

Excel

The format depends on which version of spreadsheet files you are importing.

• For xls files, the format is:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FullPathAndFileName;
  Extended Properties=Excel 8.0;HDR=Yes

• For xlsx, xlsb, or xlsm files, the format is:

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FullPathAndFileName;
  Extended Properties=Excel 12.0 Xml;HDR=Yes;IMEX=1

Where, in both cases:

• HDR defines whether the first row of the spreadsheet contains column names. When HDR=Yes, the first row is interpreted as the column names. When HDR=No, the column names are automatically assigned as F1, F2, ... Fn.
• IMEX=1 is the safe way to retrieve data from mixed data columns.

Refer to Working With Excel Files for additional information.

ODBC

ODBC is a generic driver than can be used in conjunction with the "Microsoft OLE-DB Driver for ODBC Drivers". The connection string varies according to the driver used. This example is for a connection to an Excel file using a test DSN:

DSN=test;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;

OleDB

OleDB is a generic driver that can be used with a variety of databases, such as Microsoft Access, Ingres, Paradox, and others. The prerequisite is that the corresponding OLE-DB driver has been installed and configured on the computer where the import is run. Example for Microsoft Access:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PathAndFilenameOf.mdbFile

Oracle

Oracle connections require the installation of an Oracle client provided by Oracle Corporation. The Oracle client should install the OLE-DB driver for Oracle. The format is:

Password=Password;User ID=Account;Data Source=OracleDataSourceName;
Persist Security Info=True

Tip: Do not include the provider in this connection string.

SqlServer

The format depends on the authentication model.

• For a database using Windows Authentication:

  Integrated Security=SSPI;Persist Security Info=False;
  Initial Catalog=DatabaseName;Data Source=ServerName 

  Tip: If the instance of SQL Server is not the default, append the instance name with a backslash separator:

  Data Source=ServerName\InstanceName

• For a database using SQL Server authentication:

  Password=Password;Persist Security Info=True;User ID=Account;
  Initial Catalog=DatabaseName;Data Source=ServerName

Tip: Do not include the provider in this connection string.

WebService

Web services can be called using a SOAP request. The connection string must contain the URL and, if authentication is needed, the credentials:

URL=WebServiceURI

Or, with credentials:

URL=WebServiceURI; Login=Account;Password=Password

XML

Allows for import of XML files. The connection string must contain the path and name of the XML file:

Path\FileName

Enclosing quotation marks are mandatory when the values include white space, and otherwise optional. Example:

 "C:\temp\Data.xml"