DTS xChange - The fastest way to convert DTS to SSIS
Call us  (617) 326-7393 Send email for sales inquiry  sales@pragmaticworks.com click here to suggest feature, send product feedback, report bug or ask question(s).  Product Support
Profiler Demo | DTS xChange Demo | Data Sheet | Compare MS Wizard  


Data Pump Task Migration
 
DTS xChange will migrate your DTS DataPump to DataFlow task in SSIS. Here is the list of supported mapping types in DataPump which will be migrated without any problem.
  • Copy Column Transformation
  • Upper Case Transformation
  • Lower Case Transformation
  • DateTime Transformation
  • Mid Transformation
Here is the list of mapping types which won'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 script to SSIS equivalent code. Most common approach is combination of Derived Column and Script Component. Most of time you can use Derived Column if you have simple transformation (e.g. Upper, Trim, Substring) without any complex logic. if you have complex transformation which is not possible with Derived Column then you can use script component and write VB.net/C# code.

Using Derived Column

You can use Derived column component to add new column or modify existing column in the pipeline. Derived column is faster than Script Component so if possible you should avoid scripting unless its required. Derived Column component has many predefined functions which you can use for transformation.

e.g.

String functions (e.g. Upper, Lower, Substring ...)
Example(s)

1. LOWER([ProductName])
2. LTRIM([ProductName])
3. SUBSTRING([ProductName],1,3)

DataType conversion functions
Example(s)

Syntax: <conversion function><expression>

1. Convert String to DateTime
(DT_DBTIMESTAMP) ("12" + "/31/" + "1900")

2. Convert non unicode string to unicode string
(DT_WSTR, 255)[ProductName]
Note: 255 is length of expression output.

3. Convert unicode string to nonunicode string
(DT_STR, 255,1251)[ProductName]

IF... Else (Use Ternary Operator for If Else type expression)

Syntax:  <expression> ? <true part> : <false part>

Example(s):
1. If ProductName is Blank then replace with "<UNKNOWN>"
TRIM([ProductName]) == "" ? "<UNKNOWN>" : [ProductName]


Here is the example how you can ad new column which represents current time. Under "Derived Column" you can choose either "Add New Column" or "Replace existing"



Using Script Component

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

Select input columns which will be used inside script component. Define Usage (i.e. READ/WRITE/READWRITE)



Add new column in the Output Columns (See below).

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

Following table explains how to convert various patterns found in ActiveX Script to SSIS
 

DataPump ActiveX Script Conversion Examples

DTS

SSIS
Example 1
DTSDestination("ProductID") = DTSSource("ProductID") No need to convert because there is no transformation involved in this example. Just map ProductID <-> ProductID in the Destination Adapter. If you have simple pattern like this then just remove from the script and also make sure you remove from Script Component Input Columns to improve performance.
Example 2 
If DTSSource("TransCode") = "ACT" Then
	DTSDestination("TransDescription") = "Acct Trans"
Else
	DTSDestination("TransDescription") = "UNKNOWN"
End If
 In this example TransDescription column is derived from value of TransCode. Pattern like this when you don't have exact mapping of source column to target column or Target column value is derived based on one or more source column values then the best way is use derived column. You can also use script component but give first preference to Derived Column if possible because its faster compared to script component. Some times you have to use Script component because of complexity of code. Derived column is good for simple expressions.

Using Derived Column

Step 1: Add Derived Column Component in the pipeline
Step 2: Add new column (e.g. MyTransDescription)
Step 3: Type expression as below

[TransCode]="ACT" ? "Acct Transaction" : "UNKNOWN"

Step 4: On the Destination Adapter Mappings screen map MyTransDescription to Target column TransDescription

Using Script Component

You can do same thing with Script component but it will be slower than Derived column so if possible give first preference to Derived Column.

Step 1: Select Input column TransCode (ReadOnly)
Step 2: Add New Output Column "MyTransDescription" under Input Output Columns tab and specify data type and length of the column.
Step 3: Type the following script
If Row."TransCode" = "ACT" Then
	Row.MyTransDescription = "Acct Trans"
Else
	Row.MyTransDescription = "UNKNOWN"
End If

Step 4: On the Destination Adapter Mappings screen map MyTransDescription to Target column TransDescription
 
Example 3 
If DTSSource("Discontinued") = 0 Or _
	DTSSource("Discontinued") Is Null Then

	DTSDestination("UnitPrice") = DTSSource("UnitPrice")+50
Else
	DTSDestination("UnitPrice") = Null
End If
 
If Row.Discontinued = 0 Or _
	Row.Discontinued_IsNull Then
    
	Row.UnitPrice = Row.UnitPrice + 50
Else
    Row.UnitPrice_IsNull = True
End If
Example 4 
DTSDestination("ProductName") = UCase(DTSSource("ProductName"))
 
Row.ProductName = UCase(Row.ProductName)
Example 5
DTSDestination("RecordTimeStamp") = Now() In this example source table doesn't have RecordLoadTimeStamp column and value for target field is generated at run time. For pattern like this you have to either use "Derived Column" component or use Script Component.

Using Derived Column

If you use derived column to add or modify column in the pipeline then


Using Script Component

If you decide to use script component then you have to add new column in the Output Columns (See below).

After adding column in the output column collection edit the script as below.

Row.RecordTimeStamp = DateTime.Now()
 

Related Downloads

Download sample Files
 

Home | Free Trial | DTS Profiler Demo | DTS xChange Demo | Data Sheet | Compare MS Wizard | FAQ

Copyright ® 2008 DTSxChange.com