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.