Variables and Expressions with SSIS EzAPI

In this edition of how-to recipes for the SSIS EzAPI library, I examine how to create variables at different scopes as well as how to set expressions.

Variable creation

The creation of a variable is accomplished by invoking the Add method of the Variables collection. The object from which the Add method is called determines the newly formed variable's scope. In general, one creates variables at the Package level so this code creates 2 variables at that level (the object is ezPackage).

Expressions

Mercy, expressions are ugly to write out. It's time like this that I truly miss python's ability to handle different string delimiters. Expression themselves, are not hard if you've ever written them. The code that gets assigned to the Expression property is everything that would go inside the Expression Editor within BIDS/SSDT.

The trick lies in knowing how to escape all the quotation marks. I demonstrated both the classic C style approach of escaping everything of importance with a \ which results in lots of seemingly random slashes in code. I especially enjoyed that I had to escape the file path separator to make the expression valid and then I got to double those doubles to make it valid C#. .NET also allows for a literal string to avoid the slashing problem and instead I get to replace it with a double quoting problem. Most of the time, it's not an issue except of course in the SSIS expression langauge when it's used with some regularity.

//-----------------------------------------------------------------------
// <copyright file="Driver.cs" company="billfellows.net">
//     I mention copyright so StyleCop is happy.
// </copyright>
//-----------------------------------------------------------------------
namespace EzAPIRecipes
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Dts;
    using Microsoft.SqlServer.SSIS.EzAPI;
    
    /// <summary>
    /// Driver class to demonstrate using EzAPI to build SSIS packages
    /// </summary>
    public class Driver
    {
        public static string PackagePath = @"C:\sandbox\EzAPIRecipes\EzAPIRecipes";
        /// <summary>
        /// This is the GO button.
        /// </summary>
        /// <param name="args">Command line arguments</param>
        public static void Main(string[] args)
        {
            MakePackageWithVariablesAndExpressions();
        }


        /// <summary>
        /// Build a sample package that demonstrates how to create variables at different scope levels
        /// and assign expressions.
        /// Recipe 01
        /// </summary>
        public static void MakePackageWithVariablesAndExpressions()
        {
            string outputFile = string.Empty;
            EzPackage ezPackage = null;
            
            string variableName = string.Empty;
            bool readOnly = false;
            string nameSpace = "User";
            string variableValue = string.Empty;
            string escapedExpression = string.Empty;
            string literalExpression = string.Empty;
            variableName = "RootFolder";
            variableValue = @"C:\ssisdata\ouput";

            outputFile = string.Format(@"{0}\{1}", Driver.PackagePath, @"EzAPI_Recipe01.dtsx");
            ezPackage = new EzPackage();
            ezPackage.Name = "EzAPI_Recipe01";

            ezPackage.Variables.Add(variableName, readOnly, nameSpace, variableValue);

            // Add a second variable, this time one that uses expressions
            variableName = "OuputFile";
            variableValue = string.Empty;

            ezPackage.Variables.Add(variableName, readOnly, nameSpace, variableValue);
            ezPackage.Variables[variableName].EvaluateAsExpression = true;

            // The rendered expression is as follows.
            // @[User::RootFolder] + "\\" + @[System::PackageName] + "_" + (DT_WSTR, 4)Year(@[System::StartTime]) + "-" +  RIGHT("00" + (DT_WSTR, 2)Month(@[System::StartTime]),2) + "-" + RIGHT("00" + (DT_WSTR, 2)Day(@[System::StartTime]),2) + ".txt"
            escapedExpression = "@[User::RootFolder] + \"\\\\\" + @[System::PackageName] + \"_\" + (DT_WSTR, 4)Year(@[System::StartTime]) + \"-\" +  RIGHT(\"00\" + (DT_WSTR, 2)Month(@[System::StartTime]),2) + \"-\" + RIGHT(\"00\" + (DT_WSTR, 2)Day(@[System::StartTime]),2) + \".txt\"";
            literalExpression = @"@[User::RootFolder] + ""\\"" + @[System::PackageName] + ""_"" + (DT_WSTR, 4)Year(@[System::StartTime]) + ""-"" +  RIGHT(""00"" + (DT_WSTR, 2)Month(@[System::StartTime]),2) + ""-"" + RIGHT(""00"" + (DT_WSTR, 2)Day(@[System::StartTime]),2) + "".txt""";

            // either assignment will result in an expression set on the variable
            ezPackage.Variables[variableName].Expression = escapedExpression;
            ezPackage.Variables[variableName].Expression = literalExpression;

            // Create a new variable, scoped to the Sequence Container that raises the OnChange event
            EzSequence seq = new EzSequence(ezPackage);
            seq.Variables.Add("ScopedVariable", readOnly, nameSpace, "10");
            seq.Variables["ScopedVariable"].RaiseChangedEvent = true;

            ezPackage.SaveToFile(outputFile);
        }
    }
}

In all that mess above, we create 3 variables with the following name, scope and value.

RootFolder

Static variable, defined at the package level. Value is C:\ssisdata\ouput

OuputFile

Dyanamic variable (has expression set), defined at the package level. Value is RootFolder + Package Name + date stamp

ScopedVariable

Static variable, defined on the Sequence container with an eventhandler tied to value change. Value is 10

From EzAPI

Review

We're still warming up, I'd hate to blow a hamstring by jumping straight into complex dataflows or crazy control flow logic. You should have a basic understanding of how to create variables at any scope in an SSIS package and the configuration options available to you via expressions.