A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, January 31, 2012

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).


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)

        /// <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;

In all that mess above, we create 3 variables with the following name, scope and value.
Static variable, defined at the package level. Value is C:\ssisdata\ouput
Dyanamic variable (has expression set), defined at the package level. Value is RootFolder + Package Name + date stamp
Static variable, defined on the Sequence container with an eventhandler tied to value change. Value is 10
From EzAPI


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.

1 comment:

Mark J. Miller said...

Thanks for making this available, I'm currently kicking the tires. I just wanted to suggest two tips:

1) If the assemblies aren't availble at the paths mentioned in the post you need to install "Client Tools SDK" when you're installing SQL Server. I've you've already installed SQL Server you can just re-run the installer and select the option to "Add features to an existing installation".

2) Instead of escaping all the quotes and back slashes you can just add a Resource file to your project so you don't have to escape anything. For paths you can also use the System.IO.Path.Combine method.