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
- Save the package as VB from the DTS Designer.
- Create a new VB6 Standard EXE project.
- Add a Reference to the Microsoft DTSPackage library.
- Remove the default Form1 from the project.
- Select Project\Add Module and add the saved .BAS module to project.
- Save the VB6 project.
- In VS.Net, select Open Project and select the saved VB6 project. The Upgrade Wizard launches.
- After running the Wizard, add an "Imports DTS" statement at the top of the upgraded module, if desired (after the Option statements).
- Fix the errors and warnings as described below.
Manually
- Save as VB from the DTS Designer.
- Create new VB.Net Console application.
- Add a COM Reference to the Microsoft DTSPackage library
- Delete or Exclude from Project the default Module1.
- Select Project\Add Existing Item and add the saved .BAS module to the .Net project.
- Add an "Imports DTS" statement at the top of the module, if desired.
- 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.