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.

script component settings

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.

About Paul Hernandez

I'm an Electronic Engineer and Computer Science professional, specialized in Data Analysis and Business Intelligence Solutions. Also a father, swimmer and music lover.
This entry was posted in Business Intelligence and tagged , , , , , . Bookmark the permalink.

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

  1. C# coder says:

    That’s what I’m looking for how to read or write variables in a Script task in SSIS. Thanks for your post.

  2. Pingback: Ssis User Variables C# | Sufferband

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s