How to run a DTS VB package in the .NET framework
February 10, 2003James Horne
Data Transformation Services (DTS) remain an important tool in the arsenal of database administrators and web designers that need to move data between data repositories. These repositories can include SQL Server 2000, a variety of other databases as well as text files, and Excel files. You're also being compelled to move towards the Visual Studio .NET framework as you move forward with software development based on the Microsoft platform. Therefore, you will invariably run into the problems outlined below.
With ten minutes of cleanup, you can use a DTS package generated by SQL Server 2000 within the Visual Studio .NET framework. You'll have all the benefits of running in the Visual Studio .NET framework, and be able to programmatically control the behavior of the DTS package. This article will get you there quickly while navigating a path that isn't always straight forward.
There are two important reasons why you'll want to do this.
The rest of this article explains exactly how to do this upgrade, and then how to apply some of the common edits required to bring the code completely up to standard. Microsoft has also provided documentation on the changes to the Visual Basic language. You can also reference Visual Basic .NET upgrade guide. or Upgrading Applications Created in Previous Versions of Visual Basic for further information.
Microsoft has built an upgrade tool into the .NET environment to migrate old VB code into the CLR of the .NET framework. You will need this tool since SQL Server 2000 service pack 2 still generates VB code targeted to Visual Basic 6.0. This code makes assumptions that are not valid in the .NET framework
As a test case, I created a very simple DTS package that reads two columns from one text file and writes the exact same columns into another text file using a Transform Data Task. The compiler errors in this article are typical. You may get different or additional errors depending on how you've set up your package.
Note: Creation of Interop DLL's
All the referenced DTS dlls shipped with SQL Server 2000 run as COM libraries. To interface these libraries into the Visual Studio .NET environment, wrapper dll's are created which provide the necessary metadata information required by the common language runtime to interface with the DTS COM libraries. (Interop.DTSCustTasks.dll, Interop.DTSPump.dll, and Interop.DTS.dll) The good news is that these wrappers are provided for free by the upgrade tool. More Info