Creating DTS Package Objects and Connections

DTS Programming

DTS Programming

Creating DTS Package Objects and Connections

The Package2 object is at the top of the Data Transformation Services (DTS) object hierarchy and is the first created. You then add Connection2 objects to access databases and other data sources.

Creating Package Objects

The first step in implementing a DTS package is creating the Package2 object. The way you create the Package2 object depends on your programming environment. For more information about configuring the Package2 object, see DTS Packages in Visual Basic.

Some Package2 properties and features you can use are:

  • AutoCommitTransaction, TransactionIsolationLevel and UseTransaction. These control whether Package2 components run under a package-level transaction, and how that transaction is used.

  • FailOnError and FailPackageOnLogFailure. These determine whether component failures cause package execution to fail.

  • CreationDate, CreatorComputerName, CreatorName, PackageID and VersionID. These provide creation and identification information.
Adding Connections

Usually, you create a Connection2 object for each data source you want to access, although Connection2 objects can be reused. You need an OLE DB provider for the data source you wish to access. The following table describes the typical data sources for which OLE DB providers are supplied with Microsoft® SQL Server™ 2000.

Data Source Type Data Source
Databases SQL Server
Microsoft Access 2000
Oracle
Paradox
DB2
DBase 5
Other ODBC-compliant database
Other data sources Microsoft Excel 2000 worksheet
HTML file
Text file

For more information about the OLE DB providers available on a computer system, see OLEDBProviderInfos Collection. For more information about the interfaces and schema that user-implemented OLE DB providers used with DTS must support, see ProviderID Property. For more information about the interfaces and schema that user-implemented OLE DB providers used with DTS must support, see ProviderID Property.

To create a Connection2 object, use the New method of the Connections collection of the Package2 object. Set the properties as needed. The typical properties you use are:

  • Catalog. This is typically a database name.

  • DataSource. This is a server name or a data source file specification.

  • ID. This is a numeric identifier for the connection.

  • Password, UserID, or UseTrustedConnection. These contain user authentication information.

Then, Add each Connection2 object to the Connections collection of the Package2 object.

You can reference properties unique to specific OLE DB providers through the ConnectionProperties collection of the Connection2 object.

For more information about configuring the Connection2 object, see DTS Connections in Visual Basic.

See Also

Handling DTS Events and Errors

Managing DTS Package Programs

Retrieving DTS System, Package, and Log Data