DTS Connections in Visual Basic
New Information - SQL Server 2000 SP3.
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.
Security Note When possible, use Windows Authentication.
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