none
Script Component as Transformation

    Question

  • Experts,

    I have one variable as RwDesc as Type "String" at DataFlowTask level.

    Now inside above DataFlowTask I want to assign value of this variable using ScriptComponent as Transformation and insert that value into one of the column of sql server table.

    I know how to insert into using OLDEB destination component but don't know how to assign value to the variable "RwDesc" using ScripComponent.

    Can any body help me out please?

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, August 25, 2011 4:24 PM

Answers

  • Hi SSISJoost,

    I have a user defined value, now with the script component I have assign value "Hi", now I want to insert this variable value into one of our existing table-column.

    So my question is do I need to always create output under "Input & Outputs" tab in Script component and map this variable value using below ???:-

    Row.Output-Column-Name = Variables.RwDesc

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS


    If you only want to fill a column with the value of a variable, then you could also use a Derived Column like Arthur mentioned.

    If you want to use a Script Component, then you can use the code you wrote. But it's probably better to get the value of the SSIS variable in the PreExecute method and store that in a .Net variable and use that .Net variable to fill the column in the Input0_ProcessInputRow. See this example:

    http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    • Marked as answer by Eileen Zhao Monday, September 05, 2011 8:45 AM
    Thursday, August 25, 2011 6:11 PM
    Moderator

All replies

  • Hi All,

    I selected my user variable "RwDesc" as ReadWriteVariable.

    Now inside "Edit Script", how to write code such that I can assign value "Hello World" (eg.) to the above variable.

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, August 25, 2011 4:45 PM
  • See this example of using variables in a Script Component: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Thursday, August 25, 2011 5:03 PM
    Moderator
  • Hi SSISJoost,

    Thank you for your response.

    So do I have to always define "Output" under "Input and Outputs" tab? (I have selected RwDesc variable as ReadWriteVariable)

    Can I just write below code and use my variable as output inside of creating another output inside this Script Component.

    public override void PostExecute()
      {
        base.PostExecute();
        this.Variables.RwDesc = "Hi";
       }

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, August 25, 2011 5:48 PM
  • It is just my 2c: I 'd simply use a Derived Column Transformation with type Add.

    Example:

    SSIS Expression Language and the Derived Column Transformation

    http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx

    Note: scroll down until you see the "Add column", in the SSIS Expression part just use your variable.


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, August 25, 2011 6:01 PM
    Moderator
  • Hi SSISJoost,

    Thank you for your response.

    So do I have to always define "Output" under "Input and Outputs" tab? (I have selected RwDesc variable as ReadWriteVariable)

    Can I just write below code and use my variable as output inside of creating another output inside this Script Component.

    public override void PostExecute()
     {
      base.PostExecute();
      this.Variables.RwDesc = "Hi";
      }

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    what's the purpose? Filling the variable or filling a column?
    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Thursday, August 25, 2011 6:01 PM
    Moderator
  • Kumar,

    Try this out if you are writing in VB

    Dts.Variables(

    "RwDesc").Value = "Hello World"
    Thursday, August 25, 2011 6:03 PM
  • Hi SSISJoost,

    I have a user defined value, now with the script component I have assign value "Hi", now I want to insert this variable value into one of our existing table-column.

    So my question is do I need to always create output under "Input & Outputs" tab in Script component and map this variable value using below ???:-

    Row.Output-Column-Name = Variables.RwDesc

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Thursday, August 25, 2011 6:06 PM
  • Hi SSISJoost,

    I have a user defined value, now with the script component I have assign value "Hi", now I want to insert this variable value into one of our existing table-column.

    So my question is do I need to always create output under "Input & Outputs" tab in Script component and map this variable value using below ???:-

    Row.Output-Column-Name = Variables.RwDesc

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS


    If you only want to fill a column with the value of a variable, then you could also use a Derived Column like Arthur mentioned.

    If you want to use a Script Component, then you can use the code you wrote. But it's probably better to get the value of the SSIS variable in the PreExecute method and store that in a .Net variable and use that .Net variable to fill the column in the Input0_ProcessInputRow. See this example:

    http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    • Marked as answer by Eileen Zhao Monday, September 05, 2011 8:45 AM
    Thursday, August 25, 2011 6:11 PM
    Moderator