DTS Connections

To successfully execute Data Transformation Services (DTS) tasks that copy and transform data, a DTS package must establish valid connections to its source and destination data and to any additional data sources (for example, lookup tables).

Because of its OLE DB architecture, DTS allows connections to data stored in a wide variety of OLE DB-compliant formats. In addition, DTS packages usually can connect to data in custom or nonstandard formats if OLE DB providers are available for those data sources and if you use Microsoft® Data Link files to configure those connections.

DTS allows the following varieties of connections:

• A data source connection.

  These are connections to: standard databases such as Microsoft SQL Server™ 2000, Microsoft Access 2000, Oracle, dBase, Paradox; OLE DB connections to ODBC data sources; Microsoft Excel 2000 spreadsheet data; HTML sources; and other OLE DB providers.

• A file connection.

  DTS provides additional support for text files. When specifying a text file connection, you specify the format of the file. For example:

  • Whether a text file is in delimited or fixed field format.
    
    
  • Whether the text file is in a Unicode or an ANSI format.
    
    
  • The row delimiter and column delimiter if the text file is in fixed field format.
    
    
  • The text qualifier.
    
    
  • Whether the first row contains column names.
• A data link connection.

  These are connections in which an intermediate file outside of SQL Server stores the connection string.

Configuring a Connection

When creating a package in the DTS Import/Export Wizard, in DTS Designer, or programmatically, you configure connections by selecting a connection type from a list of available OLE DB providers. The properties you configure for each connection vary depending on the individual provider for the data source.

You can configure a new connection or use an existing one. You can use the same connection multiple times in a package.

Before configuring a connection, consider the following:

• Each connection can be used by only one DTS task at a time because the connections are single-threaded. When designing a package that requires multiple task connections, consider opening up several connections and balancing the load to improve performance.
  
  
• If two tasks use the same connection, they are compelled to execute serially, rather than in parallel. If two tasks use different connections, they may execute in parallel. If two tasks use separate connections that refer to the same instance of SQL Server, they will execute in parallel. If both of these tasks have joined the package transaction, the package fails.

  For more information, see DTS Transaction Fundamentals.

• If you plan to run a package on different servers, you may need to edit the direct connections made in a package (for example, if the original data sources will be unavailable, or you will be connecting to different data sources). To simplify editing, consider using a data link file, where the connection string is saved in a separate text file. Alternately, consider using the Dynamic Properties task to change the connection information at run time.
  
  
• When scheduling a package, consider the security information you have provided. If you used Windows Authentication when configuring a connection, the SQL Server Agent authorization information is used to make the connection rather than the account information you used when designing the package. For more information, see Handling Package Security in DTS.

To create a connection