Running a DTS Package Saved as a Visual Basic File
You can run a Data Transformation Services (DTS) package that has been saved by one of the DTS tools as a Microsoft® Visual Basic® file. The saved module, a Visual Basic .bas file, consists of declarations and a Sub Main and may contain other Subs called by Sub Main. The Subs contain all the logic of the DTS package.
Here are the basic steps for incorporating a Visual Basic module file into a Visual Basic project and executing it on a computer running the Microsoft SQL Server™ client tools:
- In Visual Basic, create a new Standard EXE project.
- On the Project menu, click References, and then select the Microsoft DTSDataPump Scripting Object Library, Microsoft DTSPackage Object Library, and Microsoft DTS Custom Tasks Object Library check boxes.
Not all DTS programs will require all three of these libraries.
- On the Project menu, click Add File, and then add the Visual Basic file produced by the DTS Import/Export Wizard or DTS Designer.
- In the Project Explorer, select Form1 and then on the Project menu, click Remove Form1 to remove the blank form from the Project.
- Run the project.
No indication of completion will be given other than the Visual Basic Development Environment will go back to design mode.
You may want to add completion notification and error handling and controls to allow the user to invoke the transformation.
Using the Visual Basic File to Save to SQL Server
The Visual Basic project you created from the generated Visual Basic file can be used to save the DTS package to SQL Server.
Here are the basic steps for saving Visual Basic files to SQL Server:
- Go to the end of the Sub Main and uncomment the line
'objPackage.SaveToSQLServer ... - Comment out the following line
objPackage.Execute - Run the project.
When the Visual Basic Development Environment goes back to design mode, the package is saved to SQL Server.
The package can now be edited, maintained, and run from DTS Designer. It can be saved again as a Visual Basic file from DTS Designer.
See Also
Executing DTS Packages in Visual Basic