If you have a fairly complex DTS-package (Data Transformation Services) in SQL-Server 2000 and don’t have a clue of what it does and have a limited understanding of dts read this. You can ”export” the package to VB6 and by doing so you can read the code and run and debug the script from the VB6-IDE. You can even run it in VB.NET. This is quite old school but sometimes necessary to do in legacy systems if you want to edit a complex dts-script.
Step 1 – Export the dts-package to visual basic
Choose ”Package -> Save as” in the menu and then change ”Location” to ”Visual Basic file” in the dialog. Press OK and then a bas-file (VB-module) is saved to disk.
Step 2 – Create a visual basic project
Open Microsoft Visual Basic IDE. Choose to create a new ”Standard EXE” project then click ”Open”. Remove the default Forms1 from the project. Then you add the dts bas-file to the project with ”Project -> Add module”. The result should should look like below.
Step 2 – Executing and debugging the dts-package
Go to ”Project -> References” and add references to ”Microsoft DTSPackage Object Library” and ”Microsoft DTS Custom Task Object Library”. Now you are ready to edit your package.
To debug the package you simply set a breakpoint and hit F5.
Now feel free to fool around with the package/module and make the changes that you need. It is not easy at first but after a while you get the hang of it.
Step 3 -Saving the dts-package to SQL-server
When your package is ready for deployment you can deploy it through VB. Instead of executing the package you can easily save it to your SQL-server.
Search for the ”Save or execute package” section in the module. Comment the Execute statement and uncomment the SaveToSQLServer row.
Hit F5 to run the module. If everything works fine your new dts-package shows up in SQL-server.
More help with DTS can be found and at sqldts.com/