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

Home Page Forums FAQs – SSIS Tips and Tricks 3 Ways -SSIS Read Write Variables – Script Task C# / VB.net

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #323
    ZappySys
    Keymaster

    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

    ssis-script-task-read-write-variable-vb-csharp

    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

    Set SSIS Variable value (multi-line content using placeholder)

    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 6 years, 10 months ago by ZappySys.
    • This topic was modified 6 years, 10 months ago by ZappySys.
Viewing 1 post (of 1 total)
  • The forum ‘FAQs – SSIS Tips and Tricks’ is closed to new topics and replies.