Post reply

SSIS Equivalent for VBScript used in SQL2000 DTS Transformations

  • December 18, 2007 at 12:01 am

    #127361

    Hi,

    I am converting package from DTS2000 to SSIS 2005. DTS 2000 uses transformation scripts

    Eg:

    DTSDestination(" ").Value

    or

    DTSDestination(" ") )

    Can someone please tell me is there any equivalent for that in SSIS or how to do this kind of transformation in SSIS.

    Thanks.

  • December 18, 2007 at 5:27 am

    #760953

    You did not give much information, but you are probably looking for the Derived Column transformation.

  • December 18, 2007 at 6:04 am

    #760967

    Thank you for the reply Michael

    I am very much new to SSIS. In DTS2000 I used "DTS.DataPumpTransformScript" and added VBScript using "TransformServerProperties" to map each column in input to output. In the Script I either format the input column value or use a substitution value from global variables.

    like DTSDestination("FieldName") = Trim$(DTSSource("FieldName"))

    or

    DTSDestination("FieldName") = DTSGlobalVariables("VariableName").Value

    I want to know how to do similar transformation using SSIS Programming. I need to specify the transformation for each column

  • December 18, 2007 at 6:56 am

    #760987

    What you described can easily be done through a Derived Column task.

  • December 18, 2007 at 7:56 am

    #761030

    Thank you Michael. Derived column looks appropriate.

  • December 20, 2007 at 9:38 am

    #761815

    Hi,

    I created a derivedcolumn component and mapped the output collection of a OLEDB source to derivedcolumn component and then mapped the derivedcolumn's output to a text file. This part is working fine.

    When I add a new column to the derivedcolumn component the extract is not getting created, however i am not getting any error also.

    Here is how i am adding the new column

    For each vColumn in vInput.VirtualInputColumnCollection

    instance.SetUsageType(derivedColumnComp.InputCollection(0).ID, vInput, vColumn.LineageId,DTSUsageType.UT_READONLY)

    Next

    Dim myCol as IDTSOutputColumn90

    myCol = derivedColumnComp.OutputCollection(0).OutputColumnCollection.[New]()

    myCol.Name = "TimeStamp"

    myCol.SetDataTypeProperties(Wrapper.DataType.DT_DBTIMESTAMP,0,0,0,0)

    myCol.ExternalMetadataColumnId = 0

    Dim myProp as IDTSCustomProperty90

    myProp = mycol.CustomPropertyCollection.New()

    myProp.Name = "Expression"

    myProp.Value = "[getDate]()"

    myProp.Name = "FriendlyExpression"

    myProp.Value = "getDate()

    can somebody help!!!

  • May 14, 2008 at 8:00 am

    #815207

    hi,

    Can u tell what is equivalent in SSIS for below statement

    DTSDestination("FieldName") = DTSGlobalVariables("VariableName").Value

  • May 22, 2008 at 11:52 pm

    #819487

    You could use a derived coumn.

    - You would have a variable called varFieldName with a scope of package

    - In your derived column, you would have something like:

    YourDerivedColumn (Derived Column Name)

    @[User::varFieldName] (Expression)

Viewing 8 posts - 1 through 7 (of 7 total)

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