3 Ways -SSIS Read Write Variables – Script Task C# / VB.net

[ZappySys]

Read/Write SSIS Variable

Read/Write variable is one of the most common tasks ssis developers do using Script Task. There are two ways you can read/write variables in SSIS ScriptTask.

Method 1-Read SSIS Variable – Define in ReadOnlyVariables

Declare variables you want to read in ReadOnly Variable property of script task and then use Dts.Variables collection to read

• Pros
  • Easy to read/write variables
• Cons
  • Variables are lock through out the execution of Script Task. If you have multiple tasks reading same variables somewhere in your package then you may get this error Failed to lock variable “User::YourVariable” for read access

Method 2-Use C#/VB.net Script – Dynamic Approach

Another way of reading/writing SSIS Variable is using Lock/Unlock Method of VariableDispenser Object. This is dynamic way of doing same thing we saw in previous section.

• Pros
  • No need to list variables under ReadOnlyVariables or ReadWriteVariables
  • Very dynamic approach. Any variable can be accessed from the script task as long as it exists within the scope
  • Variable locking is done just during read/write operation compared to previous approach where variable is locked significantly longer
• Cons
  • Requires additional code

    '//Example:  Dim myVarValue As Object = "Abcd" 
    '//          WriteVariable("User::MyVar", myVarValue)
    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
        Try
            Dim vars As Variables = Nothing
            Dts.VariableDispenser.LockForWrite(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex '//Handle things your way or throw back
        End Try
    End Sub

    '//Example:  Dim myVarValue As Object = ReadVariable("User::MyVar")
    Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As Variables = Nothing
            Dts.VariableDispenser.LockForRead(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex '//Handle things your way or throw back
        End Try
        Return result
    End Function

    //Example:  object myVarValue = "Abcd"; 
    //          WriteVariable("User::MyVar", myVarValue);
    private void WriteVariable(string varName, object varValue)
    {
        try
        {
            Variables vars = null;
            Dts.VariableDispenser.LockForWrite(varName);
            Dts.VariableDispenser.GetVariables(ref vars);
            try
            {
                vars[varName].Value = varValue;
            }
            finally
            {
                vars.Unlock();
            }
        }
        catch (Exception ex)
        {
            throw ex; //Handle things your way or throw
        }
    }
    '//Example:  object myVarValue = ReadVariable("User::MyVar");
    private object ReadVariable(string varName)
    {
        object result;
        try
        {
            Variables vars = null;
            Dts.VariableDispenser.LockForRead(varName);
            Dts.VariableDispenser.GetVariables(ref vars);
            try
            {
                result = vars[varName].Value;
            }
            finally
            {
                vars.Unlock();
            }
        }
        catch (Exception ex)
        {
            throw ex; //Handle things your way or throw
        }
        return result;
    }

Method 3 – Use SSIS Logging Task

Third way to read variable for Logging purpose is use FREE SSIS Logging Task

This task allows you to read variable and log them to Execution Log, Message Box or File. This is very convenient way for debugging purpose during development of runtime of production Jobs.

SSIS Logging Task support DateTime formatting, Dumping object resultset (From ExecuteSQL resultset output) as HTML table. You can also use variable placeholders anywhere in multiline text.

To set Variable to certain text with placeholders you just have to select action as Log to [Variable] and

Above task is part of FREE SSIS Task collection provided by SSIS PowerPack. To see all FREE tasks/components check this link

Download FREE SSIS Components

• This topic was modified 7 years, 6 months ago by ZappySys.
• This topic was modified 7 years, 6 months ago by ZappySys.

[Author]

Posts