How to run a SQL Server Data Transformation Services package from Visual Basic (315661)

SUMMARY

Data Transformation Services (DTS) is a set of tools that you can use to export, to import, and to transform data between one or more data sources, such as Microsoft SQL Server or Microsoft Access, using OLE DB to provide connectivity.

You can run a SQL Server DTS package from SQL Server Enterprise Manager by using the dtsrun command from a command prompt or by using SQL Server Agent to schedule the execution. These methods allow you, as an administrator, to run packages as required or at predetermined times.

However, you may sometimes want to allow users to run packages, but the users might not have access to or knowledge of the SQL Server tools. In this situation, you may want to run a DTS package from another application.

This article demonstrates how you can run a SQL Server DTS package from within a Visual Basic application by using the DTSPackage Object Library. Not only can you run packages that are stored on a computer that is running SQL Server, in the repository, or from a file, but you can also create and modify packages by using this library.

Create a sample DTS package

Create a sample DTS package that, for the purposes of this article, is named DTSDemo.

For more information about how to create a DTS package see:

Install SQL Server client components

You must install the SQL Server client components installed on the Visual Basic development computer, if they are not installed already. This will install (among other things) the DTSPackage Object Library.

You do not have to perform this step if you have all the client components installed on your development computer.

For more information about how to install only the client components see:

Create a Visual Basic application that executes a DTS package

1. Start the Visual Basic development environment, and then create a new Windows application (Standard EXE).
2. On the Project menu, click References. Click to select Microsoft DTSPackage Object Library, and then click OK.
3. Add a new command button to the form. It will have the default name Command1.
4. Add an event handler for Command1, and then add the following code if you are using SQL Server authentication. Substitute the string values in the following code with an appropriate server name, user name, and password for your database.

   Sub Command1_Click()
       Dim dtsp As New DTS.Package
       dtsp.LoadFromSQLServer _
           ServerName:="MyServer", _
           ServerUserName:="MyUserID", _
           ServerPassword:="MyPassword", _
           PackageName:="DTSDemo"
       dtsp.Execute
   End Sub
   						

   If you are using Windows NT integrated authentication, add the following code.

   Sub Command1_Click()
       const DTSSQLStgFlag_UseTrustedConnection = 256
       Dim dtsp As New DTS.Package
       dtsp.LoadFromSQLServer _
           ServerName:="MyServer", _
           Flags:=DTSSQLStgFlag_UseTrustedConnection,
           PackageName:="DTSDemo"
       dtsp.Execute
   End Sub
   						

Run the application

Run the Visual Basic application, and then click the command button. The DTS package is loaded and executed on the server where it will generate a text export file named DumpData.txt in the root of the C drive on the computer on which SQL Server is running. This is the location that you specified when you created the package, and you can only change it by editing the package in SQL Server.