SSIS API: Getting started…with Variables

Resources:

Starting easy…
First things first, add a reference to the Microsoft.SqlServer.Dts.Runtime, parent of the Microsoft.SQLServer.ManagedDTS assembly (which can be found in the GAC; note the “SQL” capitalization).
Then, creating a variable is simple:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
Variable ssisVar = pkg.Variables.Add("myVariable", false, "User", null);

The Variables collection (found in Packages, but also Containers; more on this in a future post) contains an Add method with four parameters:
string name – variable name
bool readOnly – ReadOnly/ReadWrite
string nameSpace – namespace for variable. “User” is typical. “System” is not allowed.
Object val – design-time value for variable. Note that we can set this later

One immediate surprise is that, contrary to what we’d expect from the SSIS Designer, variables are weakly-typed! [The SSIS Designer is sneaky] Setting a value to NULL allows us to punt on making that data type choice until later:

ssisVar.Value = 3;

Once we’ve done this, we can determine the DataType of the variable (note that this is read-only).

Alternately, we can set the Variable to be the result of an
expression:

ssisVar.Value = 3;
ssisVar.EvaluateAsExpression = true;
ssisVar.Expression = "3 * 3";

though it can’t reference other Variables.

Finally, browsing for existing variables is easy:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
foreach (Variable variable in pkg.Variables)
{
    Console.WriteLine(variable.QualifiedName);
}

Leave a Reply

Your email address will not be published. Required fields are marked *