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


James Horne

Overview

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 .NET environment provides the common language runtime (CLR)
    with improved memory management. Utilizing the CLR is critical in a web-based
    application that needs to run for multiple days without running out of memory.

  • If you develop your web application against a development SQL
    Server and then run it against a production SQL Server, you’ll find it
    cumbersome to configure the data source/destination of a DTS package saved as
    meta data or in structured storage. However, you can easily configure the data
    connections in your saved VB package to reflect the various stages of your
    release process.

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.

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 hyperlinks 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. More
Info

Latest Articles