- Package2 Object
- Connection2 Object
- Task Object
- Step Object
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Connection2 Object
We use the Connection object to connect to data sources. We can connect to any data source that we have an OLE DB provider for. For our example, we will use two Connection objects—one to connect to SQL Server and one to connect to an Excel file that will be created by our package. We will export all of the data from the Customers table in the Northwind database; and combine the City, Region, Country, and PostalCode columns into a single field in the Excel file. We will create the connections as DTS.Connection2 objects. We set our connection as follows to indicate that we want a connection to a SQL Server (using the SQLOLEDB.1 driver):
Set <ConnectionObject> = <PackageObject>.Connections.New("SQLOLEDB.1")
In these connections, we will set some ConnectionProperties values. For our connection to SQL Server, we could set the Integrated Security and Persist Security Info ConnectionProperties values similar to how we would in a connection string:
.ConnectionProperties("Integrated Security") = "SSPI" and
.ConnectionProperties("Persist Security Info") = True
For our example, we use SQL Server authentication instead of Windows authentication, and use the User ID and Password properties instead of the Integrated Security property. We'll also set the Application Name value for readability, although we can leave it out if we want to. The other properties we set are the Name and Description, as we did for the Package object. Remember that, like the Package object (and all other objects), you can omit the Description property. The DataSource and Catalog properties are similar to the properties you set in a connection string. We also set the UseTrustedConnection property to match that of the type of connection. If we set the Integrated Security property to SSPI, we need to set this UseTrustedConnection property to true; because we are using SQL authentication we set this value to false.
The ID property is a number used to identify each connection. This ID is used wherever the connection is used (such as in transformations that we will discuss later), so we need to make sure that we keep these IDs straight (because each connection must have its own ID). With just two connections, it's pretty easy to remember their IDs, but if we have multiple connections, it gets cumbersome. To make things easier, I like to put my connection IDs into an enumeration, and use the enumeration for my ID. In addition, using an enumeration makes it easier to read and understand what connection is being used later on.
Set the ConnectionTimeout property to whatever meets your needs—the default is 60 seconds. We set the Reusable property to specify whether more than one task can use this connection (actually, a Step object, discussed later in this article, is what uses the connections, but we can think of the task using it for now). If we only have one processor or our DTS Package can perform only one task at a time, we can have multiple tasks use the same connection. If we need multiple tasks to run simultaneously, however, we can use a connection for each one, and get better performance. The best way to find out is to test in your environment, and tweak this property as necessary. For other connections, many of the properties are the same, but things such as the Object Creation and DataSource properties will vary with each type of connection. Also, some connections have properties that others do not.
For our Excel connection, we set the DataSource to the name of the file, and we have an "Extended Properties," in which we can set some Excel-specific properties, such as indicating that we are using the Excel 8.0 Jet driver. Again, I usually try to make a connection method generic enough so that I can use it for any similar connections.