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

Enterprise Manager

Editing a Connection

You can edit existing connections in an existing package only in DTS Designer or programmatically.

If you edit a package in DTS Designer and change the connection properties, DTS Designer attempts to connect to the server you specify. If that server is currently unavailable, DTS Designer does not allow you to change the connection properties through the Connection Properties dialog box. However, there may be reasons you want to circumvent this safety mechanism (for example, if you are configuring or editing a package to be run on a different instance of SQL Server). For those cases, use the Disconnected Edit feature to edit package properties directly. For more information, see Editing DTS Package Properties with Disconnected Edit.

When a package is executing, DTS only makes a connection when the connection is used. DTS does not pre-validate the connections. By default, connections remain open after being used, in case they require reuse. Because connections are not checked, you can set the connection properties dynamically with a Microsoft ActiveX® script (for example, by selecting a server) before making the actual connections.

If there are transformations defined between two connections and you want to change either the source or destination connection, your transformations may no longer be valid. Therefore, DTS Designer prompts you as to whether you want to reset the transformation properties to their defaults. If you decide to reset the transformation, the properties of every transformation associated with the connection are deleted. However, lookups will not be affected.

See Also

DTS Connections in Visual Basic