This document discusses issues encountered converting a simple SQL Server 2000 DTS Package, saved as Visual Basic 6.0 code, to Visual Basic .Net code. Assumes "Option Strict Off".

Contents

• The Test Package
• Importing a "Save As Visual Basic" DTS Package into Visual Studio .Net
• Using the Upgrade Wizard
• Manually
• Errors, Warnings and Fixes
• Comparison of The 2 Approaches

The Test Package

The test package copied the Northwind Customers table from Access to SQL Server, dropping and recreating the destination table. It consisted of 3 tasks: 2 Execute SQL (drop and create) and 1 Transform Data (copy).

Importing a "Save As Visual Basic" DTS Package into Visual Studio .Net

Using the Upgrade Wizard

1. Save the package as VB from the DTS Designer.
2. Create a new VB6 Standard EXE project.
3. Add a Reference to the Microsoft DTSPackage library.
4. Remove the default Form1 from the project.
5. Select Project\Add Module and add the saved .BAS module to project.
6. Save the VB6 project.
7. In VS.Net, select Open Project and select the saved VB6 project. The Upgrade Wizard launches.
8. After running the Wizard, add an "Imports DTS" statement at the top of the upgraded module, if desired (after the Option statements).
9. Fix the errors and warnings as described below.

Manually

1. Save as VB from the DTS Designer.
2. Create new VB.Net Console application.
3. Add a COM Reference to the Microsoft DTSPackage library
4. Delete or Exclude from Project the default Module1.
5. Select Project\Add Existing Item and add the saved .BAS module to the .Net project.
6. Add an "Imports DTS" statement at the top of the module, if desired.
7. Fix the errors and warnings as described below

Errors, Warnings and Fixes

Note: It may be helpful to sort the messages in the Task List window by message, by clicking on the column header. You can double-click on a particular message to go directly to the offending line of code.

Message: "Argument not specified for parameter 'Index' of Public Overridable Overloads Function Item(Index As Object) As DTS.Step
Occurs on:
   tracePackageError(goPackage.Steps.Item)
Solution: change back to original code:
   tracePackageError(goPackage)

Message: "Constructor call is valid only as the first statement in an instance constructor"
Occurs on a line such as:
   oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Comments:
   Actually a type-casting issue
Solution - Ctype:
   oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
Also occurs on:
   oTransformation = oCustomTask3.Transformations.New("DTS.DataPumpTransformCopy")
Solution:
   oTransformation = CType(oCustomTask3, DTS.DataPumpTask2).Transformations.New("DTS.DataPumpTransformCopy")

Message: "Interface <interface> cannot be indexed because it has no default property.
Occurs on a line such as:
   oConnection.ConnectionProperties("Mode") = 1
Comments: The COM interface does not expose the Item method as its default property.
Solution - Item and Value (when a value is being set):
   oConnection.ConnectionProperties.Item("Mode").Value = 1
Also occurs on a line such as:
   oStep = goPackage.Steps("Create Table [Northwind].[dbo].[Customers2] Step")
Comments: Referencing an item in a collection, not setting a value.
Solution - Item only:
   oStep = goPackage.Steps.Item("Create Table [Northwind].[dbo].[Customers2] Step")

Message: "Expression is a value and therefore cannot be the target of an assignment."
Occurs after you add the ".Item" method as mentioned above:
   oConnection.ConnectionProperties.Item("Mode") = 1
Comments: You added ".Item" but forgot to add ".Value", didn't you?
Solution, as above:
   oConnection.ConnectionProperties.Item("Mode").Value = 1

Message: Name 'DTSStepExecResult_Failure' is not declared.
Comments: Enum constants need to be prefixed with Enum name.
Solution:
   DTSStepExecResult.DTSStepExecResult_Failure

Comparison of The 2 Approaches

Using the Upgrade Wizard

1   "Argument not specified..." error
4   "Constructor call is valid only..." errors
-------------------------------------------
5   Total changes required

Manually

4   "Constructor call is valid only..." errors
13  "Interface '<interface>' cannot be indexed..." errors
1   enum value to prefix with enum name
-------------------------------------------
18  total changes required

Disclaimer: This document is provided "AS IS" with no warranties, and confers no rights.