Search Suggest

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

Post a Comment