September 21, 2006 at 1:24 pm
I have several DTS packages that perform data transformation when loading data from a fixed field flat file into a table. I need to migrate the packages to SSIS and find a way to maintain the transformations. Any suggestions on how to copy the ActiveX scripts into SSIS. I am not sure how to convert the ActiveX to the new script component. Here is a snipit of the old VB Code:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
Dim temp1
DTSDestination("ActNum1") = DTSSource("Col002")
DTSDestination("ActNum2") = DTSSource("Col003")
' minus the 144 next columns
'next columns with a lot of code like this
'fields that get a constant
DTSDestination("DBSource") = "A"
DTSDestination("SystemName") = "STRS"
'fields that need date conversion
temp1 = DTSSource("Col033")
temp1 = Left(temp1,2) & "/" & Mid(temp1,3,2) & "/" & Right(temp1,4)
if ISDATE(temp1) then
DTSDestination("BirthDt") = temp1
end if
'logic to get CurrentStatus and CurrentStatusDt
temp1 = Null
temp1 = DTSSource("Col041")
if temp1 = Chr(32) then
temp1 = "A"
DTSDestination("CurrentStatus") = temp1
else
DTSDestination("CurrentStatus") = temp1
end if
select case temp1
case "A"
temp1 = DTSSource("Col037")
case "I"
temp1 = DTSSource("Col072")
case else
temp1 = DTSSource("Col044")
end select
if ISDATE(temp1) then
DTSDestination("CurrentStatusDt") = Left(temp1,2) & "/" & Mid(temp1,3,2) & "/" & Right(temp1,4)
end if
DTSDestination("Plan_Name") = "TEACHERS"
DTSDestination("Db_Source") = "A"
DTSDestination("Cabinet_Type") = "ACTIVE"
Main = DTSTransformStat_OK
End Function
September 25, 2006 at 8:00 am
This was removed by the editor as SPAM
September 26, 2006 at 7:43 am
The Derived Column Transform component will do this. You put it between the data source and the destination, and use expressions to define new columns. The destination will see both the original columns and the new columns as inputs, you have to make sure you connect the transformed columns to the destination columns.
If you have something more complicated that a simple expression can't handle, you can use a script transform component. It requires more programming, but is much more powerful.
September 26, 2006 at 8:20 am
That's where I am having the issue. I have tried to copy the ActiveX script into the script component but it looks like the language/format is different.
September 26, 2006 at 9:11 am
If it was as simple as copying the script, the upgrade wizard could do it for you. SSIS scripting is done in VB.NET instead of VBScript.
Most of it is not much different. (Heavy emphasis on the weasel words). If you reference any package variables or the DTS object model, it is completely different.
There's no magic bullet here, you'll have to actually read the documentation and learn how SSIS works. These web sites have can help you:
http://blogs.conchango.com/jamiethomson/default.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply