DTS Connections in Visual Basic
Add a Connection object for each database or other OLE DB store you want to access. ODBC data sources can be accessed through the Microsoft® OLE DB provider for ODBC (MSDASQL).
Here are the basic steps for adding a Connection object in Microsoft Visual Basic®:
- Declare an object variable of the appropriate type.
Use the Connection class in the object variable declaration if the application must be compatible with Microsoft SQL Server™ version 7.0. Use Connection2 if it is to run only with SQL Server 2000.
- Create the object with the New method of the Connections collection of the Package2 object. Pass the programmatic identifier (ProgID) of the appropriate OLE DB provider to New as an argument.
- Set properties to identify the connection and data source.
For more information, see Connection2 Object.
- Use the Add method of the Connections collection of the Package2 object to add the Connection object to the package.
Creating a Connection with the Microsoft OLE DB Provider for SQL Server
The following code example shows you how to create a connection using the Microsoft OLE DB Provider for SQL Server. The New method references a specific version of the SQLOLEDB provider. If you do not need a specific version, you should use the version-independent ProgID, in this case "SQLOLEDB" rather than "SQLOLEDB.1":
'Declare the object variable.
Private objConnect As DTS.Connection2
Private objPackage As DTS.Package2
. . .
'Create the connection object. The package is already created at this point.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
Set objPackage.Connections.Add = objConnect
Creating a Connection with the Microsoft OLE DB Provider for Jet
The following code example shows you how to create a connection using the Microsoft OLE DB Provider for Jet:
'Establish a connection to the Access database.
Set objConnect = objPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
objConnect.ID = 2
objConnect.DataSource = "D:\DTS_UE\Data\JetPubs.mdb"
Set objPackage.Connections.Add = objConnect
Creating a Connection with the Data Shaping Service for OLE DB
The following code example shows you how to create a connection using the Microsoft Data Shaping Service for OLE DB. The example also illustrates how you can use the ConnectionProperties collection to access the properties of the specific OLE DB provider:
Set objConnect = objPackage.Connections.New("MSDataShape")
With objConnect
.ConnectionProperties("Data Provider") = "SQLOLEDB"
.ID = 1
.Catalog = "Northwind"
.UserID = "sa"
End With
objPackage.Connections.Add objConnect
After the Connection object has been added to the Connections collection, the object variable is no longer needed and can be reused for another connection or set to Nothing to release its reference.