DTS Flat File ActiveX Transformation...

  • 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


      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

  • This was removed by the editor as SPAM

  • 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.

  • 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.

  • 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:




Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply