Creating DTS Package Objects and Connections
New Information - SQL Server 2000 SP3.
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.
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
Other ODBC-compliant database
|Other data sources||Microsoft Excel 2000 worksheet |
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.
Security Note When possible, use Windows Authentication.
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.