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.