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); }