Microsoft KB Archive/315661

= How to run a SQL Server Data Transformation Services package from Visual Basic =

Article ID: 315661

Article Last Modified on 8/4/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q315661





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:

SQL Server Books Online

&quot;Creating DTS Packages in Visual Basic&quot;

&quot;DTS Packages in Visual Basic&quot;

&quot;Creating a Package with DTS Designer&quot;

&quot;Creating a DTS Package with the DTS Import/Export Wizard&quot;

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:

SQL Server 2000 Books Online

&quot;How to Install Client Tools Only (Setup)&quot;

SQL Server 7.0 Books Online

&quot;How to Install Client Connectivity Components (Setup)&quot;

Create a Visual Basic application that executes a DTS package
 Start the Visual Basic development environment, and then create a new Windows application (Standard EXE). On the Project menu, click References. Click to select Microsoft DTSPackage Object Library, and then click OK. Add a new command button to the form. It will have the default name Command1.  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:=&quot;MyServer&quot;, _ ServerUserName:=&quot;MyUserID&quot;, _ ServerPassword:=&quot;MyPassword&quot;, _ PackageName:=&quot;DTSDemo&quot; 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:=&quot;MyServer&quot;, _ Flags:=DTSSQLStgFlag_UseTrustedConnection, PackageName:=&quot;DTSDemo&quot; 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.

