Thursday 30 December 2010

How to use variables in a Script Task

Case
Every now and then you need a variable in a script task. For instance to avoid a hardcoded path or connectionstring in your code. You can use Package Configuration to fill a variable and use that variable in your script task.

Solution
There are two ways to read and write variables in a Script task. I will show you both.

1) Create variables
Let's create to string variables called ReadVariable and WriteVariable.
Right mouse click in Control Flow












2) Script task
Add a script task to your package and give it a suitable name.
The Script Task
















3a) Simple version
Add ReadVariable to the ReadOnlyVariables and add WriteVariable to the ReadWriteVariables:
Let the script know what variables you want to use.



















Now the code is only one row:
// C# code
public void Main()
{
    // Fill WriteVariable with value from ReadVariable
    Dts.Variables["User::WriteVariable"].Value = Dts.Variables["User::ReadVariable"].Value;

    Dts.TaskResult = (int)ScriptResults.Success;
}

' VB.Net code
Public Sub Main()
    ' Fill WriteVariable with value from ReadVariable 
    Dts.Variables("User::WriteVariable").Value = Dts.Variables("User::ReadVariable").Value

    Dts.TaskResult = ScriptResults.Success
End Sub

3b) Advanced Version
In the advanced version you don't add the variables to the list. We will do that in the code:
// C# code
public void Main()
{
    // Lock variables
    Dts.VariableDispenser.LockForRead("User::ReadVariable");
    Dts.VariableDispenser.LockForWrite("User::WriteVariable");

    // Create a variables 'container' to store variables
    Variables vars = null;

    // Add variables from the VariableDispenser to the variables 'container'
    Dts.VariableDispenser.GetVariables(ref vars);

    // Now you can use the variables
    vars["User::WriteVariable"].Value = vars["User::ReadVariable"].Value;

    // Release the locks
    vars.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
}

' VB.Net code
Public Sub Main()
    ' Lock variables 
    Dts.VariableDispenser.LockForRead("User::ReadVariable")
    Dts.VariableDispenser.LockForWrite("User::WriteVariable")

    ' Create a variables 'container' to store variables 
    Dim vars As Variables = Nothing

    ' Add variables from the VariableDispenser to the variables 'container' 
    Dts.VariableDispenser.GetVariables(vars)

    ' Now you can use the variables 
    vars("User::WriteVariable").Value = vars("User::ReadVariable").Value

    ' Release the locks 
    vars.Unlock()

    Dts.TaskResult = ScriptResults.Success
End Sub
You can even add a Try Catch Finally construnction to avoid unexpected errors (variable doesn't exists or is already locked). And with the finally you can release the locks even if your script fails, so you can still use the variable in for example an event handler.
// C# code
public void Main()
{
    // Create a variables 'container' to store variables 
    Variables vars = null;
    try
    {
        // Lock variables 
        Dts.VariableDispenser.LockForRead("User::ReadVariable");
        Dts.VariableDispenser.LockForWrite("User::WriteVariable");

        // Add variables from the VariableDispenser to the variables 'container' 
        Dts.VariableDispenser.GetVariables(ref vars);

        // Now you can use the variables 
        vars["User::WriteVariable"].Value = vars["User::ReadVariable"].Value;
    }
    catch (Exception ex)
    {
        // Throw an exception or add some logging
        throw ex;
    }
    finally
    {
        // Release the locks  (even if your script task fails)
        vars.Unlock(); 
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

' VB.Net code
Public Sub Main()
    ' Create a variables 'container' to store variables 
    Dim vars As Variables = Nothing

    Try
        ' Lock variables 
        Dts.VariableDispenser.LockForRead("User::ReadVariable")
        Dts.VariableDispenser.LockForWrite("User::WriteVariable")

        ' Add variables from the VariableDispenser to the variables 'container' 
        Dts.VariableDispenser.GetVariables(vars)

        ' Now you can use the variables 
        vars("User::WriteVariable").Value = vars("User::ReadVariable").Value
    Catch ex As Exception
        ' Throw an exception or add some logging
        Throw ex
    Finally
        ' Release the locks (even if your script task fails)
        vars.Unlock()
    End Try

    Dts.TaskResult = ScriptResults.Success
End Sub

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 method is used more often in blogs and forums. I recommend using the simple method.

Note: Variables in a Script Component will be discussed at a later time.
Update: I wrote an SSIS WIKI about this where I also show how to use parameters in a Script Task

10 comments:

  1. Thanks for the info, great help!!!

    ReplyDelete
  2. Error:

    en Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()
    en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.Variables.set_FInicial(DateTime Value)
    en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
    en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
    en SC_a692aa72c9074d25a8f50e490e2fef5d.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
    en Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    ReplyDelete
    Replies
    1. Can't help you with this. It isn't the actual error message. Best option is to ask a question in this forum and give much more details (like code, screenshot, etc.).

      You could debug your script task or add a try catch with some logging to find the error.

      Delete
  3. Mine just hangs. Doesn't do anything . . .

    ReplyDelete
    Replies
    1. It's probably locked (by an other task). Please post the code and a description/screenshot of your package in the in the MSDN SSIS Forum.

      Delete
  4. Thanks, I went successfully with your steps.

    ReplyDelete
  5. Hi,

    This is the first time I am on this blog....
    I have used the same logic to count the row based on the condition here is the code below. I am not able to access the variables after the update the variables in the script component. Please help.... I am using the variable in the next component to update the column. which is updating as 0.
    =======================================================
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain
    Inherits UserComponent
    Dim X As Integer = 0
    Dim Y As Integer = 0

    Public Overrides Sub PostExecute()
    MyBase.PostExecute()

    'Me.Variables.XX= X
    'Me.Variables.YY= Y
    Me.ReadWriteVariables("XX").Value = X
    Me.ReadWriteVariables("YY").Value = Y

    MessageBox.Show(Me.Variables.XX.ToString)
    MessageBox.Show(Me.Variables.YY.ToString)
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    If (Row.Location = "A") Then
    X= X+ 1
    ElseIf (Row.Location = "B") Then
    Y= Y+ 1
    End If
    End Sub

    End Class
    =============================================
    Thank you,

    ReplyDelete
    Replies
    1. You can only access variables that are changed by the Script Component after the Data Flow Task is completely finished (in the next task or in a precedence constraint going to the next task).

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