DTS xChange Migrating

Task Migration

DTS xChange tries to migrate most of your tasks. If a task can't be migrated, DTS xChange leaves the task embedded in the DTS object in the converted SSIS Package.

The following table displays the tasks that can be fully converted and the tasks that may require manual work:

ActiveX Script Task

How to convert ActiveX Script to native SSIS

DTS xChange converts the ActiveX Script task to the ActiveX Script Task provided in SSIS for legacy support. In certain cases ActiveX in SSIS does not work and you may have to modify the script or replace the ActiveX Script entirely with native SSIS Tasks.

The following table lists the most common patterns found in the ActiveX script:

What doesn't work in SSIS ActiveX Script Task?

DTS Object Model Calls Examples:

• Set pkg = DTSGlobalVariables.Parent  '// Will not work in SSIS
• Set conn = opkg.Connections("MyConnection") '// Will not work in SSIS
• Set stp = opkg.Steps("DTSExecuteSQL_1") '// Will not work in SSIS

DTS Variable Assignment to Int64 or Single DataType variable

You must perform proper casting or change the variable datatype to fix this error. You may receive the following error(s) in some cases when you try to execute ActiveX Task in SSIS:

• [ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001868CC.
• [ActiveX Script Task] Variable uses an Automation type not supported in VBScript

What works in SSIS ActiveX Script Task?

Any COM object created using CreateObject function

• Example : CreateObject("Scripting.FileSystem")

Variable read/write using DTSGlobalVariable (Old way to access global variable)

• Example : Msgbox "Customer ID is " & DTSGlobalVariables("gvCustID").Value

How to convert Scripting.FileSystem object of ActiveX Script to native SSIS Task

Developers use the Scripting.FileSystem object in the ActiveX script task to perform various file system related tasks (Copy file, Delete file etc). In SSIS you can perform common file system related tasks using the File System task. If any task you're performing using Scripting.FileSystem is not possible using the File System Task (Check File Exists), then you can use Script Task in SSIS and use System.IO namespace to perform File/Folder related tasks that aren't possible using the FileSystem Task.

You can perform any of the following operations using the FileSystem Task:

The following code snippets display how to perform some of most common file/folder related tasks that aren't possible to implement using File System Task:

Path related functions

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        Dim sMyVar As String '//Get only file name from a specified path => Returns mydatafile_001.txt
        sMyVar = System.IO.Path.GetFileName("c:\temp\mydatafile_001.txt")  '//Get only directory path from a specified path => Returns c:\temp
        sMyVar = System.IO.Path.GetDirectoryName("c:\temp\mydatafile_001.txt")  '//Combine two paths into one path => Returns c:\temp\mydatafile_001.txt
        sMyVar = System.IO.Path.Combine("c:\temp", "mydatafile_001.txt")  '//Get filename without extension => Returns mydatafile_001
        sMyVar = System.IO.Path.GetFileNameWithoutExtension("c:\temp\mydatafile_001.txt")  '//Get extension of the file => Returns txt
        sMyVar = System.IO.Path.GetExtension("c:\temp\mydatafile_001.txt")         Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Check if file/folder exists

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        Dim sMyVar As String        If File.Exists("c:\temp\file_001.txt") = True Then
            '//Debug.Print "File Exists"
        End If        If Directory.Exists("c:\temp") = True Then
            '//Debug.Print "Folder Exists"
        End If        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Read from text file

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        '//Read file content to string variable
        Dim sMyfileData As String
        Dim sReader As StreamReader = New StreamReader("c:\temp\file_001.txt")
        sMyfileData = sReader.ReadToEnd
        sReader.Close()        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Read from text file (line by line)

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        '//Read file line by line
        Dim sReader As New StreamReader("c:\autoexec.bat")
        ' Display all the text lines in the file.
        Do Until sReader.Peek = -1
            ' The ReadLine methods reads whole lines.
            Console.WriteLine(sReader.ReadLine)
        Loop
        ' Always close a StreamReader when you've done with it.
        sReader.Close()        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Write to text file

Imports System.IOPublic Class ScriptMain
    Public Sub Main()        '//Open existing file for append. If file doesn't exist then new file will be created
        Dim writer As StreamWriter = New StreamWriter("c:\write_test.txt", True)
        writer.WriteLine("Hello world - line1")
        writer.WriteLine("Hello world - line2")
        writer.Close()        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Get file information

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
   
        '//Get file properties
        Dim sInfo As String
        Dim FileProps As FileInfo = New FileInfo("c:\windows\notepad.exe")
        sInfo = sInfo & " File Name = " & FileProps.FullName
        sInfo = sInfo & " Creation Time = " & FileProps.CreationTime
        sInfo = sInfo & " Last Access Time = " & FileProps.LastAccessTime
        sInfo = sInfo & " Last Write Time = " & FileProps.LastWriteTime
        sInfo = sInfo & " Size = " & FileProps.Length        System.Diagnostics.Debug.Write(sInfo)        FileProps = Nothing        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

List files (with wild card search pattern and recursive option)

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        Dim file As String
        '//Recursive listing (search for *.txt)
        '//Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.AllDirectories)        '//Top level listing (search for *.txt)
        Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.TopDirectoryOnly)
        For Each file In files
            System.Diagnostics.Debug.WriteLine(file & "...found")
        Next
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

List sub directories

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        Dim dir As String
        'Dim dirlist() As String = Directory.GetDirectories("c:\windows","*",SearchOption.AllDirectories)
        Dim dirlist() As String = Directory.GetDirectories("c:\windows")
        For Each dir In dirlist
            System.Diagnostics.Debug.WriteLine(dir)
        Next
       
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

List disk drives

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        Dim dirInfo As Directory
        Dim drive As String
        Dim drives() As String = dirInfo.GetLogicalDrives()
        For Each drive In drives
            System.Diagnostics.Debug.WriteLine(drive)
        Next        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

File/Folder delete, copy move

Imports System.IOPublic Class ScriptMain
    Public Sub Main()
        If File.Exists("c:\dest\datafile.txt") Then
            File.Delete("c:\dest\datafile.txt")
        End If
        File.Copy("c:\src\datafile.txt", "c:\dest\datafile.txt")
        File.Move("c:\src\datafile.txt", "c:\dest\datafile.txt")        If Directory.Exists("c:\dest") Then
            Directory.Delete("c:\dest")
        End If
        Directory.CreateDirectory("c:\dest")
        Directory.Delete("c:\dest")
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

How to convert ADODB object of ActiveX Script to native SSIS Task

Using ADO objects inside an ActiveX Script task is very common in DTS. The most common uses for ADO objects (ADODB.Connection, ADODB.RecordSet etc) are:

• Creating connections at runtime
• Executing Adhoc Queries at runtime
• Processing certain data row by row

Sample ActiveX Script using ADODB.Connection and ADODB.Recordset

The following example performs three actions:

• Establishes Connection to SQL Server using ADODB.Connection object
• Executes SQL Query and Load the recordset using ADODB.Recordset object
• Loops through each record to build HTML formatted string and emails it

'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************Function Main()
    Dim objRs
    Dim objConn
    Dim strSql
    Dim strHtml
   
    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=SQLOLEDB;Data Source=(local);Trusted_Connection=yes;Initial Catalog=Northwind"
   
    strSql = "Select top 10 b.ProductName,Sum(a.UnitPrice*a.Quantity) as Total from [Order Details] a "
    strSql = strSql & "join Products b on a.ProductID =b.ProductID "
    strSql = strSql & "group by b.ProductName order by Sum(a.UnitPrice*a.Quantity) desc"
   
    '--- //Method 1 : Using Execute Method of Connection Object
    '--- Set objRs=objConn.Execute(strSql)
    '--- //
   
    '*** /// OR ///****
   
    '//Method 2 : Using Execute Method of Connection Object
   
    Set objRs = CreateObject("ADODB.Recordset")
    objRs.Open strSql, objConn
    '//
   
    strHtml = "<h2>Top 10 Products<h2><table>"
    strHtml = strHtml & "<tr><td>Product Name</td><td>Total</td></tr>"
    Do While Not objRs.EOF
        strHtml = strHtml & "<tr><td>" & objRs(0) & "</td><td>" & objRs(1) & "</td></tr>"
        objRs.MoveNext
    Loop
    strHtml = strHtml & "</table>"
   
    'MsgBox strHtml    SendMail "dataservices@mycompany.com", "ceo@mycompany.com", "Top 10 Products", strHtml, True
    Main = DTSTaskExecResult_Success
End FunctionSub SendMail(FromAddress, ToAddress, Subject, Body, IsHtml)
    '//Write Code to send email
End Sub

The following table lists how to migrate various patterns you encounter in DTS:

Convert Sample DTS ActiveX script to SSIS equivalent control flow

Install the Sample Package

Download the ADO_Object_Replace.zip sample package. After downloading the package, complete the following steps:

1. Extract the Sample zip file.
2. Open FAQTest.sln and analyze or run the Sample Package to test.

Step by Step

Step 1:  Define Variables 

1. The sample package uses four variables with the following settings:

Step 2: Define Connections: The sample package requires one connection.

1. Create an oledb connection ((local).Northwind) to use Northwind database.

Step 3:  Execute SQL Task (Get Recordset) 

1. Create an execute SQL task to execute the following SQL Statement. Make sure you select the Northwind connection for this task.

Select top 10 b.ProductName,Sum(a.UnitPrice*a.Quantity) as TotalSales
from [Order Details] a
join Products b on a.ProductID =b.ProductID
group by b.ProductName order by Sum(a.UnitPrice*a.Quantity) desc

Step 4: Foreach Loop Container (Loop Recordset)

1. Place the Foreach Loop Container and double click it to open the properties dialog box.
2.  Apply the following settings displayed in the screenshots.

Select Enumerator, ADO object Source Variable and Enumerator mode

Select Variable Mappings

Step 5: Script Task (Update HTML String): Place a Script Task inside the Foreach Loop Container.

1. Double click the Script Task
2. Specify ReadOnlyVariables as displayed in the following screenshot  (varProdName,varTotal)
3. Specify ReadWriteVariables as displayed in the following screenshot (strHTML)
4. Select the Design Script Button
5. Enter the script displayed below

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain
    Public Sub Main()
        Dim ProdName As Object
        Dim Total As Object
        Dim HTML As Object
        ProdName = Dts.Variables("varProdName").Value
        Total = Dts.Variables("varTotal").Value
        HTML = Dts.Variables("strHTML").Value        HTML = HTML.ToString & "<tr><td>" & ProdName.ToString & "</td><td>" & Total.ToString & "</td></tr>"        Dts.Variables("strHTML").Value = HTML        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

6. Script Task (Send HTML Email): Place the Script Task to execute SendHTMLEmail routine

1. Place a Script Task
2. Double click the Script Task
3. Specify strHTML in the ReadOnlyVariables
4. Select the Design Script Button
5. Enter the script displayed below

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain
    Public Sub Main()
        Dim HTML As String
        Dim Header, Footer As String
        Header = "<h2>Top 10 Products<h2><table><tr><td>Product Name</td><td>Total</td></tr>"
        Footer = "</table>"
        HTML = Dts.Variables("strHTML").Value.ToString        HTML = Header & HTML & Footer        SendMail("dataservices@mycompany.com", "ceo@mycompany.com", "Top 10 Products", HTML, True)        Dts.TaskResult = Dts.Results.Success
    End Sub    Sub SendMail(ByVal FromAddress As String, _
        ByVal ToAddress As String, _
        ByVal Subject As String, _
        ByVal Body As String, _
        ByVal IsHtml As Boolean)        MsgBox(Body)
        '//Write Code to send email
    End SubEnd Class

7. Connect all tasks and testing  

1. Connect all tasks as shown and execute the package to test.

Data Pump Task

How to mDTS xChange migrates your DTS DataPump to a DataFlow task in SSIS. The following list displays the supported mapping types in DataPump that are migrated without any problems:igrate complex DataPump with ActiveX Script Transformations

• Copy Column Transformation
• Upper Case Transformation
• Lower Case Transformation
• DateTime Transformation
• Mid Transformation

The following mapping types can't be migrated properly with DTS xChange:

• ActiveX Script Transformation
• Import File Transformation
• Export File Transformation

Converting ActiveX Script Transformation

There are many approaches to convert ActiveX scripts to SSIS equivalent code. The most common approach is a combination of Derived Column and Script Component. Most of time you can use Derived Column if you have simple transformation (Upper, Trim, Substring) without any complex logic. If you have a complex transformation that's not possible with Derived Column then you can use script component and write VB.net/C# code.

Using Derived Column

Use the Derived column component to add new columns or modify existing columns in the pipeline. Derived column is faster than the Script Component. Avoid scripting unless it's required. The Derived Column component has many predefined functions that you can use for the transformation:

Add a new column that represents the current time. Under Derived Column you can select Add New Column or Replace existing.

Using Script Component

To use the script component you must first select input columns and define output columns. In most of cases you don't have to define output columns unless you are adding new columns in the pipeline. In the following example, the new column RecordTimeStamp is added. Anything defined in the Input or Output column list can be accessed inside the Script component for READ or WRITE (this can be change by changing column usage property).

Select the input columns that will be used inside script component. Define Usage (READ/WRITE/READWRITE).

Add new column in the Output Columns.

After adding the column in the output column collection, edit the script.

The following table explains how to convert various patterns found in ActiveX Script to SSIS:

DataPump ActiveX Script Conversion Examples

Data Driven Query Task

How to migrate Data Driven Query Task to native SSIS

Purpose

The Data Driven Query task allows you to perform flexible, Transact-SQL based operations on data, including stored procedures and INSERT, UPDATE or DELETE statements. For each row in a source row set, the Data Driven Query task selects, customizes, and executes one of several SQL statements. Select statements that you want to execute through a constant return value set in a Microsoft ActiveX script transformation. Based on the return constant used in the script, one of four different parameterized SQL statements that you create can be executed for each source row.

Summary

DTS xChange converts a Data Driven Query to an embedded DTS object. The Data Driven Query Task has been discontinued in SSIS. You may want to rewrite the task using native SSIS tasks and components. The Step-by-Step example section covers how to use native SSIS components to convert a Data Driven Query Task manually.

Step-by-Step Example

This section displays how to convert a Data Driven Query task to a Data Flow, and apply various transformations. The following items are covered in this example:

• Converting Lookups
• Perform Insert/Update Operations based on certain condition for each row
• Skip rows if no condition match
• How to do copy column mappings
• How to do special mappings (Upper Case)

Description	Image
Original sample DTS Package
Data Driven Query Task Source tab
Data Driven Query Task Binding tab
Data Driven Query Task ActiveX script transformation tab
Data Driven Query Task Queries tab
Data Driven Query Task Lookup Queries tab

The challenging part of converting a Data Driven Query Task is the ActiveX Script Transformation. Notice that in this source code that the Main function is returning three different status based on a certain condition:

'**********************************************************************
'  Visual Basic Transformation Script
'**********************************************************************
'  Copy each source column to the destination column
Function Main()
 If  DTSSource("CategoryID") =1 or DTSSource("CategoryID") =2 Then '//For Category 1,2 : Insert
  '//Special mapping
  DTSDestination("ProductName") = UCase(DTSSource("ProductName"))  '//Copy column mappings
  DTSDestination("ProductID") = DTSSource("ProductID")
  DTSDestination("SupplierID") = DTSSource("SupplierID")
  DTSDestination("CategoryID") = DTSSource("CategoryID")
  DTSDestination("QuantityPerUnit") = DTSSource("QuantityPerUnit")
  DTSDestination("UnitPrice") = DTSSource("UnitPrice")
  DTSDestination("UnitsInStock") = DTSSource("UnitsInStock")
  DTSDestination("UnitsOnOrder") = DTSSource("UnitsOnOrder")
  DTSDestination("ReorderLevel") = DTSSource("ReorderLevel")
  DTSDestination("Discontinued") = DTSSource("Discontinued")  '//Get CategoryName from Lookup
  DTSDestination("CategoryName") = DTSLookups("lookupCategory").Execute(DTSSource("CategoryID"))  Main = DTSTransformstat_InsertQuery
 ElseIf DTSSource("CategoryID") =3 Then  '//For Category 3 : Update Price by 2%
  Main = DTSTransformStat_UpdateQuery
 Else  '//For other category dont do anything
  Main = DTSTransformstat_SkipRow
 End If
End Function

In our example:

• OLEDB Destination is used to handle DTSTransformstat_InsertQuery
• OLEDB Command  is used to handle DTSTransformstat_UpdateQuery , you can also use the same component to handle DTSTransformstat_DeleteQuery
• Conditional Split  is used to divert records to certain flow based on condition. Default output of conditional flow is DTSTransformstat_SkipRow in our example
• Derived Column is used to handle special transformation (Upper, Mid, Lower). If you can't handle transformations using inbuilt functions of derived column then you may want to consider Script Transformation

DTS xChange can convert everything except the Data Driven Query Task. Complete the following steps to replace the Data Driven Query task with a DataFlow:

Step 1: Set up the Test Database

1. Download Create_Northwind_Schema_And_Data.zip and run it in a new SQL Server Management Studio query window to your local instance (or select different server). This script creates the northwind database and populates sample data.

Step 2: Migrate the DTS Package using DTS xChange

1. Migrate the DDQ_Sample_Packages.zip using DTS xChange. 

Step 3: Delete the Data Driven Query Task and add the DataFlow task

1. Delete the Data Driven Query Task container, and add a new DataFlow task in the package surface. Rename the DataFlow to Process Products. 

2. Attach a Create Table task to the Data Flow task.

Step 4: Add OLEDB source

1. Select the DataFlow and add the OLEDB Source Adapter from the toolbar.

2.  Double click on the source and select CONN_SRC in the connection dropdown. Select the Products table in the Table or View Dropdown.

Step 5: Add Derived column

1. Add the derived column component and name it Special Transformation. Add a new Replace ProductName expression, this changes the ProductName to upper case. 

2. Connect OLEDB Source with derived column.

Step 6: Add Conditional Split

1. Add a conditional split component. Attach the Derived column with the conditional split0. Double click on the conditional split to configure various outputs based on conditions. Note:  The ActiveX script above has two IF conditions and one default (ELSE block). We will do the same thing here in the conditional split and give it meaningful name for each output.

2. Create two outputs as diplayedbelow. The SSIS uses expression language very similar to C++ or C# language syntax. Enter Skip Rows in the default output and select OK to the save changes.

Step 7: Add Lookup 

The Data Driven Query Task ActiveX Script Transformation uses Lookup to find CategoryName by sourcing CategoryID. Use the Lookup Component found in SSIS but without passing a parameter.

1. Drop the Lookup component. Attach the Conditional Split component to the Lookup component, and select the Input.  Select the Insert Rows output and select OK. 

2. Double click Lookup component to open the configuration options. Enter the query displayed below:

3. Select the columns tab and drag the CategoryID column from Input columns to Lookup columns. Select the CategoryName column, and then select OK to save your changes.

Step 8: Add OLEDB Destination

1. Add the OLEDB Destination adapter and rename it to Insert Products.

2. Double click the component to open the property configuration options. 

3. Select CONN_DEST in the connection manager dropdown.

4. Select PRODUCTS_DDQ_TEST from the Table/View drop-down menu. 

5. Attach the Lookup component to the OLEDB Destination.

Step 9: Add OLEDB Command

The OLEDB command can be used to execute SQL Statements for each row in the input pipeline. 

1. Add the OLEDB Command component and  rename it to Update Products. Double click the component to open the property configuration options. 

2. Select CONN_DEST in the connection manager dropdown. Select the Component properties tab  and enter the following SQL Statement in the SQLCommand Property:

UPDATE [Northwind].[dbo].[Products_DDQ_TEST]
SET [UnitPrice]=[UnitPrice] + 0.01
Where [ProductID]=?

3. Attach ProductID column with Param_0 on the column mappings tab, and then select Ok to save your changes. 

4. Attach the Conditional Split to the OLEDB command. Select Update Rows from the Input list once prompted.

Step 10: Final Control Flow 

1. Select Debug > Start Debugging to run the package. 

Connection Migration

DTS xChange migrates all of your DTS connections to the equivalent SSIS connection.

Flat File Connection

The Flat File connection has been dramatically changed in SSIS. You have to create columns for the Flat File connection before you can use the component. Creating columns was not required in DTS but SSIS made some architectural changes to get better performance. DTS xChange automatically generates all of the columns for the flat file based on several factors. DTS xChange determines the columns for the flat file by doing the following:

• Checks DTS Metadata to get column names, length, and number of columns
• Checks mappings and validates metadata
• Reads Sample rows from the file if the metadata that's retrieved from the DTS package is invalid

UDL Connection

The UDL Connection is similar to the SSIS Config file where you can store connection strings and reuse them across multiple packages. The UDL Connection has been depreciated in SSIS. DTS xChange converts UDL Connections to the OLEDB Connection Manager. 

ODBC Connection

The ODBC Connection has limited support in SSIS. It's highly recommended to use OLEDB Providers whenever possible in SSIS. Using an ODBC connection as a destination to load data will not work in SSIS. ODBC is only supported as a Source in SSIS by using ADO.NET Connection for ODBC Connection Wrapper.

DTS xChange creates an ADO.NET Connection if an ODBC Connection is detected in DataPump Source. ADO.Net DataReader is used to load data using ODBC Driver.

OLEDB Connection

SSIS supports most of the OLEDB drivers without any problems. It's recommended that you check the Supported Data Provider Support List For SSIS.

Variable Migration

DTS xChange migrates all of your DTS variables. The following actions are performed during variable migration: 

• Com Object Data Type is converted to Object Data Type
• Invalid characters are removed from variable names. SSIS only allows AlphaNumeric characters and underscore ( _ ) in the variable name.
• Empty variables without any data type are changed to System.String data type

The following table displays DTS to SSIS Data Type Mapping performed by DTS xChange: