Tutorials » How to run a DTS VB package in the .NET framework

Introduction

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.

Upgrade Steps:

1. Use SQL Enterprise Manager to edit your DTS Package and make sure that it works. I personally like to get things working as well as possible in this environment because it's harder to manually make changes to the saved VB package.
2. Save the package as a VB program dtsPackage.bas. (You can use any name.)
3. Create a VB .exe project in Visual Basic 6.0.
4. Reference into the new project "Microsoft DTS Custom Tasks Object Library," "Microsoft DTSDataPump Scripting Object Library", and "Microsoft DTSPackage Object Library."
5. Include dtsPackage.bas in the new project.
6. Compile and run the project. This will give you confidence that the package will really work in the Visual Basic 6.0 environment.
7. Save/close the project.
8. Launch Visual Studio .NET.
9. Open the 6.0 project from Visual Studio .NET. This will automatically invoke the upgrade wizard. The upgrade wizard will do the following:
   
   • Create a new project folder and copy all the relevant Visual Studio 6.0 files into this folder.
   • Create a new project and solution file.
   • Create Interop dll's for each COM library that was referenced by the Visual Studio 6.0 project.
   • Upgrade dtsPackage.bas as well as any other VB code in the project. The upgraded code has hyper links to guide you to more descriptive documentation about the nature of the problems found.
   • Create an upgrade report that summarizes what happened in the upgrade (_UpgradeReport.htm).
10. Fix the remaining errors as outlined below.
11. Compile/run the project within Visual Studio .NET.

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.

Contents

1. Overview
2. Introduction
3. Making the fixes
4. Warnings left in the code
5. Summary

	1 2 3 4 5