Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 10, 2003

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

By DatabaseJournal.com Staff

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.


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


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


Public Sub Main()
oConnection = goPackage.Connections.New("DTSFlatFile")
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


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


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


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.


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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM