Saturday 1 January 2011

How to use variables in a Script Component

Case
I already explained how to use variables in a Script Task, but of cource you can use them in a Script Component as well.

Solution
There are two ways to read and write variables in a Script component. I will show you both. There is one big BUT for setting variables in a Script Component. You can't get the new variable value untill the Data Flow Task is finished. So you can't get its value within the same Data Flow Task.

1) Create variable
Let's create a integer variable called Counter.
Right mouse click in the Data Flow














2) Script component
Add a Script Component to your dataflow and give it a suitable name. In this example the type is  Transformation and the source is a csv file with some productnames and colors.
Script component



















Select the columns you need in the script component. In this case we count blue colored products, so I need the color columns as readonly input column.
Input Columns























Let the script know what variables you want to use.





















// C# code
public class ScriptMain : UserComponent
{
    // Counter variable for blue colors
    private int BlueCounter = 0;

    // When all rows are processed, set counter variable
    public override void PostExecute()
    {
        base.PostExecute();
        this.Variables.Counter = BlueCounter;
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Count blue items
        if (Row.Color == "Blue")
        {
            // Add 1 to the BlueCounter variable
            BlueCounter++;
        }
    }
}

' VB.Net code
Public Class ScriptMain
    Inherits UserComponent

    ' Counter variable for blue colors 
    Private BlueCounter As Integer = 0

    ' When all rows are processed, set counter variable 
    Public Overrides Sub PostExecute()
        MyBase.PostExecute()
        Me.Variables.Counter = BlueCounter
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Count blue items
        If (Row.Color = "Blue") Then
            ' Add 1 to the BlueCounter variable 
            BlueCounter = BlueCounter + 1
        End If
    End Sub
End Class


4b) Advanced Version
In the advanced version you don't add the variable to the ReadWrite list. We will do that in the code:
// C# code
public class ScriptMain : UserComponent
{
    // Counter variable for blue colors
    private int BlueCounter = 0;

    // When all rows are processed, set counter variable
    public override void PostExecute()
    {
        base.PostExecute();

        // Lock the variable for write
        VariableDispenser variableDispenser = (VariableDispenser)this.VariableDispenser;
        variableDispenser.LockForWrite("User::Counter");

        IDTSVariables100 vars;
        variableDispenser.GetVariables(out vars);

        // Set the variable
        vars["User::Counter"].Value = BlueCounter;

        // Unlock the variable
        vars.Unlock();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Count blue items
        if (Row.Color == "Blue")
        {
            // Add 1 to the BlueCounter variable
            BlueCounter++;
        }
    }
}

' VB.Net code
Public Class ScriptMain
    Inherits UserComponent
    Private BlueCounter As Integer = 0

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        ' Lock the variable for write 
        Dim variableDispenser As VariableDispenser = CType(Me.VariableDispenser, VariableDispenser)
        variableDispenser.LockForWrite("User::Counter")

        ' Use IDTSVariables90 if you're using SSIS 2005 
        Dim vars As IDTSVariables100
        variableDispenser.GetVariables(vars)

        ' Set the variable 
        vars("User::Counter").Value = BlueCounter

        ' Unlock the variable 
        vars.Unlock()
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Count blue items
        If (Row.Color = "Blue") Then
            ' Add 1 to the BlueCounter variable 
            BlueCounter = BlueCounter + 1
        End If
    End Sub
End Class
You can even add a Try Catch Finally construction to avoid unexpected errors (variable doesn't exists or is already locked). And if you move the vars.Unlock() row to the finally part, then you're certain that the variables are unlocked. Even if the Script Component fails. For an example see the last script of this Script Task article.

Which version is best? The result of both version is the same. The 'advanced' version gives you a little more control over what is happening and at what stage the variables are locked and released, but the simple version is used more often in forums and blogs. I recommend the simple method!

Update: I also wrote an SSIS WIKI about this where I also show how to use parameters in a Script Component

14 comments:

  1. Hi,

    This post relates to working with variables inside of a Script Task TRANSFORMATION (Data Flow), and not a Script Task COMPONENT (Control Flow).

    The difference is that the UserComponent base class isn't visible inside of a Script Component, and so, all of your VBscript methods fail.

    ReplyDelete
    Replies
    1. I think you are a bit confused... A Script Task is part of the Control Flow and a Script Component is part of the Data Flow. This post is about the Script Component. Go to this page for the Script Task: http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html

      Delete
    2. This comment has been removed by a blog administrator.

      Delete
  2. Hi,
    I followed your tutorial but when I run my package, the Input0_ProcessInputRow method did not process. Thank you

    ReplyDelete
    Replies
    1. Mail me (via Contect Me in menu) your code and the purpose of your script.

      Regards,

      Joost

      Delete
  3. Hi Joost,

    thanks for the tutorial! Do you know how (1) to get a list of all variables or (2) to get a list of all readable or writeable variables within a script component to use it by a foreach command?

    Regards,

    Matthias

    ReplyDelete
    Replies
    1. Hi Matthias,

      It's not possible to loop through all variables in a package, but it is probably possible to loop through all variables that are selected. Haven't done it will try it and let you know if I have some time left in the comming days. If it is more urgent post a question in the SSIS MSDN forum.

      Regards,

      Joost

      Delete
    2. This is worthless. What kind of Script Transformation? Source? Destination? So many other details LACKING.

      Delete
    3. Hi mister Anonymous,

      Thank you for your constructive feedback. There is a great website called http://msdn.microsoft.com and you will find all your answers over there. And there is also a great book about scripting in SSIS that you could buy/read: http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254

      Regards,

      Joost

      Delete
  4. Hi Josh, i find your tutorials very helpful, so don't stop making them cause of tards :)

    in this one i have one question.
    i want to use the variables during execute fase. But in this tutorial the variables are used post execution. i just want to read this variables so that i can get some values:)

    ReplyDelete
  5. ok i managed to do it by replacing the variables as readandwrite to writeonly.
    thanks ;)

    ReplyDelete
  6. This is terrific. Thank you very much.

    Ned

    ReplyDelete
  7. Hi Joost
    Thanks for the tutorial, my package is reading records from multible files that are located in one folder. Therefore, my package has a foreach loop that access multible files. I can count all records from the first file, when the new file starts, the counter goes back to zero(which is a problem because my database table require a unique id). I am trying to keep the count when my package proceeds to a new file.

    also, in the tutorial, we set the input, but how about the output.


    best regards.

    ReplyDelete
    Replies
    1. It's best to ask this question in the MSDN forum...

      How are you counting the records? RowCount transformation? Easiest is to use a Script Task after the Data Flow Task and add the number of your variable to an other variable.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...