Read and Write variables in a Script Component in SSIS (SQL Server Integration Services) using C#

The Script Component is a SSIS Data flow component and it differs from the Script Task in a Control Flow. It has some limitations to read and write values from package or data flow task level variables.

There is more than one way to read and write variables, the easiest way is to add the desired variables to the ReadOnlyVariables and ReadWriteVariables in the custom properties of the component as is needed.

The limitation of this method is that The ReadWrite variables can only be used in the PostExecute method (The ReadOnly variables can be read from anywhere in the script).

A better option, at least for me, is to use the variable dispenser. The methods are a little bit tricky but at the end they bring more flexibility.

When the variable dispenser is used adding the variables to the ReadOnlyVariables and ReadWriteVariables is not needed. I successfully tested the following methods for read and write:

//Write a value in the specify variable.
//Example: this.WriteVariable("User::receiptKey", Row.TransactionExtId.ToString())
private void WriteVariable(String varName, Object varValue)
{
  IDTSVariables100 vars = null;
  VariableDispenser.LockForWrite(varName);
  VariableDispenser.GetVariables(out vars);
  try
  {
    vars[varName].Value = varValue;
  }
  catch(Exception ex)
  {
    MessageBox.Show(ex.Message.ToString());
  }
  finally
  {
    vars.Unlock();
  }
}

//Read a variable and return its value.
//Example: currentReceipt = this.ReadVariable("User::receiptKey").ToString();
private Object ReadVariable(String varName)
{
  Object varValue;
  IDTSVariables100 vars = null;
  VariableDispenser.LockForRead(varName);
  VariableDispenser.GetVariables(out vars);
  try
  {
    varValue = vars[varName].Value;
  }
  catch (Exception ex)
  {
    varValue = null;
    MessageBox.Show(ex.Message.ToString());
  }
  finally
  {
    vars.Unlock();
  }
  return varValue;
}

Hope you find it useful.

Advertisement