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.

EzAPI Overview

I started looking at EzAPI for programmatically creating SSIS packages and I think it's an efficient product to work with. I have built SSIS packages purely through code before and up to a point, it's fun. After that, swimming in the ocean with a bad case of road rash is to be preferred.

Prerequisites

Pre-reqs are simple, you need to have something that can compile code and the EzAPI assembly.

• Visual Studio 2008/Visual Studio 2010/SharpDevelop
• SQL Server 2008 SSIS libraries (C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies). This might be optional, I have not tried, but it would depend on your usage.
• EzAPI library. Start with the installer but you'll most likely want to step up to the source code if the base functionality doesn't scratch your itch
  • EzAPISetup.msi installer
  • EzAPI source code

Getting Started with EzAPI

Create a new project, I went with Console application as it's my standby. Anything that "runs" will work.

From EzAPI

Inside that project, add a reference to the following DLLs from the .NET tab.

From EzAPI

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.DTSRuntimeWrap

EzAPI

Where are my references?

If you don't see those DLL's listed, look at the message that says this list is filtered. No problem, just change the target framework from 4.0 Client profile to the normal one.

From EzAPI

Right-click on the project and select Properties. Change the Target framework from ".NET Framework 4 Client Profile" to ".NET Framework 4".

No, really, where are they?

As you may be able to tell, I started writing this post using VS2010 as I'm trying to make it my default VS instance now that Denali/Engine of the Devil/SQL 2012 is coming. The only problem is, with 2010 they've changed the way Visual Studio handles references. I gave up trying to make heads or tails out of it and just went and added the Assemblies via the Browse tab. It's not idea but I no longer care, I just want it resolved. I went in and manually added

• c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
• c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
• C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Samples\EzAPI\EzAPI.dll

If you are not a 64 bit OS, then omit the " (x86)" path in this series. These two articles cover a bit of what they were doing with VS2010 and references but that will be my adventure for another evening.

http://blogs.msdn.com/b/jason_howell/archive/2010/08/18/visual-studio-2010-solution-build-process-give-a-warning-about-indirect-dependency-on-the-net-framework-assembly-due-to-ssis-references.aspx

http://msdn.microsoft.com/en-us/library/wkze6zky.aspx

On with the show

Code

I'm using C# here but any .NET language will suffice. For your first taste of the EzAPI, we'll start dirt simple and create a package that has nothing in it. Yes, be still your beating heart. We'll assign two properties as the package level and save to a file. The only thing that is required is to add the EzAPI namespace to our project (Microsoft.SqlServer.SSIS.EzAPI) and then start referencing a bevy of Ez* named classes.

On line 36 we declare a variable of type EzPackage and instantiate it on line 38. Lines 41 and 42 we assign values to the properties and on 43 we save the object to a file on disk. Once the code has executed, add the resulting package into a .dtproj and you have created your first package without using BIDS/SSDT.

   1:  //-----------------------------------------------------------------------

   2:  // <copyright file="Driver.cs" company="billfellows.net">

   3:  //     I mention copyright so StyleCop is happy.

   4:  // </copyright>

   5:  //-----------------------------------------------------------------------

   6:  namespace EzAPIRecipies

   7:  {

   8:      using System;

   9:      using System.Collections.Generic;

  10:      using System.Linq;

  11:      using System.Text;

  12:      using Microsoft.SqlServer.Dts;

  13:      using Microsoft.SqlServer.SSIS.EzAPI;

  14:      

  15:      /// <summary>

  16:      /// Driver class to demonstrate using EzAPI to build SSIS packages

  17:      /// </summary>

  18:      public class Driver

  19:      {

  20:          /// <summary>

  21:          /// This is the GO button.

  22:          /// </summary>

  23:          /// <param name="args">Command line arguments</param>

  24:          public static void Main(string[] args)

  25:          {

  26:              MakeSimpleEzAPIPackage();

  27:          }

  28:   

  29:          /// <summary>

  30:          /// Build the most basic of SSIS packages. Assign a name and description to the package and

  31:          /// nothing else. 

  32:          /// </summary>

  33:          public static void MakeSimpleEzAPIPackage()

  34:          {

  35:              string outputFile = string.Empty;

  36:              EzPackage ezPackage = null;

  37:              outputFile = @"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\SimpleEzAPIPackage.dtsx";

  38:              ezPackage = new EzPackage();

  39:   

  40:              // Assigning a Description leads to a value of <EzName></EzName>Content here

  41:              ezPackage.Description = "I was built using EzAPI";

  42:              ezPackage.Name = "SimpleEzAPIPackage";

  43:              ezPackage.SaveToFile(outputFile);

  44:          }

  45:      }

  46:  }

One thing you can observe is the values written to those properties. Name has SimpleEzAPIPackage but look at the Description, that's not what we assigned. There are quirks in the library and this is one of them.

From EzAPI

As a contrast, the following code represents building the same package using the shipped assemblies. There's little difference between the two sets of code, beyond of course the second correctly assigns the Description...

        /// <summary>
        /// This is the classic approach to building a package
        /// </summary>
        public static void MakeSimplePackage()
        {
            string outputFile = string.Empty;
            Package package = null;
            Application app = null;

            outputFile = @"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\SimplePackage.dtsx";
            package = new Package();
            app = new Application();

            package.Description = "I was built using the straight .NET api";
            package.Name = "SimplePackage";
            app.SaveToXml(outputFile, package, null);
        }

Finally, you could have simply right-clicked on the project, selected Add new package, clicked Properties and assigned these values but everyone knows that route.

Convinced of the cost savings yet? Of course not, tune in to the rest of the series to see cost benefit of using EzAPI.