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

Monday Feb 10th 2003 by DatabaseJournal.com Staff
Share:

Many articles on the web describe how to run a Data Transformation Services (DTS) package saved in a structured storage file or as meta data in SQL Server. Now, learn how to run a DTS package that you've saved as Visual Basic, from within ASP.NET.

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

James Horne

Making the fixes.

After the upgrade is done, you can't simply compile the new project and run it. You'll get compiler errors that must be fixed. The rest of this article will dig into four types of issues:

  • Compile errors that you must fix to get the package working. We'll tell you how to fix them.
  • Warnings left as comments in the code. We'll tell you whether you need to worry about them or not.
  • Upgrade notes left as comments in the code. In general, you should carefully consider these notes to ensure that the code is correct.
  • Conversions that took place as part of the upgrade. We'll review them as they illustrate the differences between Visual Basic 6.0 and Visual Basic .NET

How to fix the compiler errors

Three errors must be resolved before you can run the DTS package in the .NET environment. Fixing these problems should take no more then 10 minutes.

Compiler error #1 Argument not specified

Change:

tracePackageError(goPackage.Steps.Item)

To:

tracePackageError(goPackage)

TracePackageError() takes a DTSPackage as an input argument, but the code that was generated tries to pass a Step object. Simply changing the argument to the container package solves the problem and allows the tracePackageError method to walk through all the steps of the package correctly.

Compiler error #2 and #3: Constructor call is valid only as the first statement in an instance constructor

The error message is misleading. The solution is.

Change:

Public Sub Task_Sub1(ByVal goPackage As Object)
:
oTask = goPackage.Tasks.New("DTSDataPumpTask")

To:

Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)
:
oTask = goPackage.Tasks.New("DTSDataPumpTask")


and change:

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
:
oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformScript")

To:

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As DTS.DataPumpTask2)
:
oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformScript"))
:
oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformScript")

Both cases occur because a formal parameter is declared as a generic "Object." In Visual Basic 6.0 this worked because it took advantage of the default property that could be used as a shortcut in the code. Visual Basic .NET no longer supports the default property. The simple solution is to replace the "Object" formal parameter with the actual object required.

James Horne

Warnings left in the code

Warning #1: Couldn't resolve default property on Add More details

You can ignore this warning.

The add method of the Connections collection takes as input a Connection object. The DTS package uses a Connection2 object. Connection2 is derived from Connection so in theory it should be fine to add it.

GoPackage.Connections.Add(oConnection)

Upgrade notes

Upgrade Note #1: Object oConnection may not be destroyed until it is garbage collected. More details

Remember that one of the advantages of the .NET framework is the built in garbage collection. Because of this, the following statement is obsolete.

oConnection = Nothing

Change

Public Sub Main()
oConnection = goPackage.Connections.New("DTSFlatFile")
goPackage.Connections.Add(oConnection)
oConnection = Nothing
:
oConnection = goPackage.Connections.New("DTSFlatFile")

to:

Public Sub Main()
oConnection = goPackage.Connections.New("DTSFlatFile")
goPackage.Connections.Add(oConnection)
:
oConnection2 = goPackage.Connections.New("DTSFlatFile")

In the Visual Basic 6.0 environment, the above statement would immediately destroy oConnection. In the .NET framework, it only marks it for garbage collection. The proper fix is to call the Dispose() method on the object. Unfortunately, this does not exist.

In addition, if you step through the code you see that the Connections collection keeps references to the oConnection object instead of making a copy of it. Therefore, in the .NET framework if this old object is returned on the next call to New(), and then you modify this object you'll be corrupting data in the stored Connections collection.

What should you do? In most cases, you would want to worry about your data being overwritten. However, in this particular case the package also has a reference to the connection, so even setting it to Nothing in the Main() procedure will have no effect. The fix is to remove the call to oConnection = Nothing. The next call to New() will in fact return a new connection object.

For free conversions:

The converter does resolve most of the upgrade issues. We'll review what happened during the conversion process because it's an excellent illustration of the differences between Visual Studio 6.0 and Visual Studio .NET

Conversion #1 ... remove all the Set's for object assignment More details

This

Set goPackage = goPackageOld

was changed to:

goPackage = goPackageOld

In Visual Basic 6.0 the Set keyword distinguishes an assignment between the default properties of two objects from assigning the whole object. Visual Basic .NET doesn't support default properties. Therefore, if the object is referenced on the left and right side of the assignment statement, the whole object will be copied, and the Set keyword can be removed.

Conversion #2 ... .....Value as required when used on a LHS

This

oConnection.ConnectionProperties("Data Source") = "c:\fred.txt"

was changed to:

oConnection.ConnectionProperties("Data Source").value = "c:\fred.txt"

When a statement unambiguously references a default property, the upgrade tool adds the actual default property name. The code is much more readable and it conforms to the .NET framework rules. In the above case, a ConnectionProperty is actually a reference to an OleProperty object. The default property on this object is the "value" property.

Conversion #3 ... Parameters to a function must appear inside ()'s

This:

MsgBox "text string"

was changed to:

MsgBox ("text string")

The parameters passed into a procedure must be enclosed in ()'s. This is a straightforward upgrade.

Conversion #4 ... Use of "ByRef" in subroutine parameter calls.

This:

Public Sub tracePackageError (oPackage As DTS.Package)

was changed to:

Public Sub tracePackageError (ByRef oPackage As DTS.Package)

Visual Basic .NET provides better protection of parameters by using the default declaration of ByVal for all parameters. Visual Basic 6.0, by default, assumed all parameters were passed ByRef. Therefore, when the passing mechanism isn't specified, the upgrade tool adds a "ByRef" to keep the parameter passing compatible with the intent of the Visual Basic 6.0 code.

Summary

Following a bit of a winding path and making three changes will get your DTS package up and running in the .NET environment in no time. Studying what was changed and the comments inserted into the code by the upgrade tool provide a valuable example of the changes from Visual Basic 6.0 to Visual Basic .NET.

Additional Resources:


About the author:
James Horne is the President and a Principal Consultant of Horne Consulting. He has applied over 22 years of industry experience and advanced education to the software challenges of companies ranging from Fortune 500 to start-ups. He is published by McGraw Hill and holds US Patent 431341234-45.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved