Generating SSIS Packages using ezAPI – Hello World

Introduction

This is the first in a series of posts describing how to use EzAPI to generate SSIS Packages. This is the classic ‘Hello World’ example describing the very basic getting started usage of ezAPI. The article first describes the simple ‘Hello World’ example then describes a  ‘Hello World’ example incorporating Dimodelo Data Warehouse Studio design data.

What is EzAPI? EzAPI is an API released by the Microsoft SSIS Team which makes it easy to generate SSIS packages programmatically.

I guess, first, the question that comes to mind is… why would you want to write code to generate SSIS packages when you have a perfectly good GUI (i.e. Microsoft Business Intelligence Development Studio BIDS) to build these packages graphically?

The answer is… if you have a mechanism for capturing a Data Warehouse and ETL design, and can feed that design into a EzAPI class that generates a SSIS package based on that design information, then you can use the same class to generate multiple SSIS packages, and automate development.

The advantages of the SSIS package generation approach are:

• Development is orders of magnitude quicker.
• Maintenance is far easier. Just modify the design and regenerate.
• Architectural Standards and Patterns can be embodied in the EzAPI classes leading to code consistency and quality.
• Testing is greatly reduced because the single EzAPI class is unit tested code, rather than every SSIS package. As long as the EzAPI class is trusted, then the SSIS packages can be trusted.
• The need for high end SSIS Skills is reduced (other than the person developing the EzAPI class).

Simple Hello World example

I’m not a C# expert, but I do know enough to muddle through. This might be a familiar situation for others who have a career focused on Data Warehousing rather than C# development. Hopefully my explanations are understandable. Fortunately I have C# developers in Dimodelo Solutions to help me out when needed. If you want to download the completed class, rather than create it yourself, simply register below, it is attached to  this post.

1. First you need to install the EzAPI Assembly and prepare a C# project with the appropriate references. See this Getting Started with EzAPI post for details. This step is mandatory, nothing works otherwise.
2. Add a new class to your project called HelloWorldEzAPIExample.cs.
3. Add the following ‘using’ namespace directives:

   using Microsoft.SqlServer.SSIS.EzAPI;
   using Microsoft.SqlServer.Dts.Design;
   using Microsoft.SqlServer.Dts.Runtime;

4. Modify the class to inherit from EzAPI EzPackage.

   public class HelloWorldEzAPIExample : EzPackage

5. As per the EzAPI documentation add the following constructors.

   // Provide this constructor only if you want to overload Assignment operator
   public HelloWorldEzAPIExample(Package p) : base(p) { }
   
   public HelloWorldEzAPIExample() : base() { }

6. The genreated ‘Hello World’ SSIS package will contain a single ExecSQL task. Add an ezExecSqlTask as a public property of the class.

   // All the tasks, components and connection managers which should automatically be
   // assigned when SSIS package is assigned to EzPackage MUST BE PUBLIC MEMBERS.
   
   public EzExecSqlTask ExecSQL; //Execute SQL Command task

7. This is where the action happens. We diverge a little from how these classes are implemented in the official EzAPI documentation. The reason will become evident when I demonstrate the same ‘Hello World’ class with Dimodelo Data Warehouse Studio design data. Add an Execute method to the class, as shown below. Each code line of the method is described with comments.

   public  string Execute()
    {
        // Add an ExecSQL task to control flow of this Package
        ExecSQL = new EzExecSqlTask(this);
   
        // Assign a name to the ExecSQL command
        ExecSQL.Name = "Hello World" ;
   
        //Return this package back to the caller of this method.
        //The returned string contains the SSIS Package XML.
        return this.SaveToXML();
   
    }

8.  Next Add a Main method. You wouldn’t normally need this method, but it makes it easier to test the class. The Main method simply calls the Execute method and then saves the result to a file.

    static void Main(string[] args)
    {
        /********************************
         Execute the Hello World Example
        *********************************/
        HelloWorldEzAPIExample helloWorldPkgGenerator = new HelloWorldEzAPIExample();
   
        string HelloWorldPkg = helloWorldPkgGenerator.Execute();
   
        System.IO.StreamWriter file = new System.IO.StreamWriter("HelloWorld.dtsx");
        file.WriteLine(HelloWorldPkg);
        file.Close();
   
        System.Console.WriteLine("Hello World Generation Complete");
   
     }

 Generate SSIS package

1. First set the start object of your project, so when you build and debug the project, it runs your class’s Main method. Right click the project node in the Solution Explorer, and select Properties.
2. In the Startup Object dropdown select the class you just created – HelloWorldEzAPIExample.
3. Save the Project properties.
4. Press Ctrl + F5 to build and run the Main method of your class.
5. Everything going well, there should now be a new SSIS package called HelloWorld.dtsx in the project folder under binDebug.
6. Open the SSIS package in BIDS. It should contain a single EXEC Sql task with the name ‘Hello World’.

We’ll done. Your first generated SSIS package.
Obviously this is a very simple example, but EzAPI can handle complex scenarios. Dimodelo Data Warehouse Studio implements complex Dimension and Fact ETL patterns using EzAPI.
The rest of this article discusses the same Hello World example, but instead of using a static name for the ExecSQL task, the name of the task is set from the design data of a Data Warehouse designed in Dimodelo Data Warehouse Studio. It discusses how to get access to the Dimodelo Data Warehouse Studio design data, and how to extract specific information from it.

Simple Hello World example with Dimodelo Data Warehouse Studio

As I said in the introduction, generating SSIS packages is of limited value, unless you have captured Data Warehouse design data in a format that can be passed to an EzAPI class, making the class useful for generating multiple packages.

This section describes how you can modify the simple ‘Hello World’ example to pull design data from a Data Warehouse design XML document generated using Dimodelo Data Warehouse Studio. The design XML document contains all the details of the Facts and Dimensions in the Data Warehouse, as well as the ETL source to target mapping etc needed to load data into the Data Warehouse.Dimodelo Data Warehouse Studio comes with a set of ezAPI classes, and is extensible so you can incorporate your own.

Prerequisites

The prerequisites include the prerequisites described for the simple ‘Hello World’ example above plus…

To start you need the free community edition of Dimodelo Data Warehouse Studio installed on your machine. You can either register through the link below, or through the download link above. Once registered you will have access to the protected content on this page where you will find a copy of a completed C# project including:

• the HelloWorldEzAPIExample.cs class file from the exercise above
• the Dimodelo_Meta_Data.xml file containing Dimodelo Design Data for a test Data Warehouse.
• the completed HelloWorldEzAPIExampleWithDimodelo.cs

Getting started

1. Add HelloWorldEzAPIExample.cs to your project if it doesn’t already exist, or copy it if it does.
2. Rename HelloWorldEzAPIExample.cs (or the copy) to HelloWorldEzAPIExampleWithDimodelo.cs.
3. Open HelloWorldEzAPIExampleWithDimodelo.cs and do a find/replace all HelloWorldEzAPIExample to HelloWorldEzAPIExampleWithDimodelo.
4. Right click the Reference folder and click Add Reference.
5. Click the .NET tab. Select Dimodelo.MetaData and click OK.
6. Add Dimodelo_Meta_Data.xml file attached to this post to the project. Right click Dimodelo_Meta_Data.xml and select Properties. In the  Copy to Output Directory field select Copy Always.

Modify HelloWorldEzAPIExampleWithDimodelo.cs

1. Add the following using namespace directives:

   using Dimodelo.MetaData;

2. Modify the class to also inherit from Dimodelo.MetaData.ezAPIGenerationTemplate. ezAPIGenerationTemplate is an interface with a single Execute method. All ezAPI generation templates executed by Dimodelo Data Warehouse Studio must adhere to this interface.

   public class HelloWorldEzAPIExample : EzPackage, ezAPIGenerationTemplate

3. The Execute method takes a DimodeloMetaData object and a ‘Generate For’ string as its input. The DimodeloMetaData object wraps a Dimodelo_Meta_Data.xml file. Replace the existing Execute method with the code below.
   Note how the code extracts design data for a Fact table from the metaData parameter, and then uses the name of the Fact table in the name of the ExecSQL task in the generated SSIS package.

   public  string Execute(DimodeloMetaData metaData, string generateForUid)
   {
   
       // Get the Dimodelo Meta Data as an XElement. There are other options.
       //The Meta Data can be retrieved as an XML string, or an XPathNavigator also.
       XElement DimodeloMetaDataXML = metaData.getXElement();
   
       //Extract out of DimodeloMetaDataXML, using XLINQ syntax, the XML for the Fact that the
       //ezAPI template is generating for (identified by the unique identifier passed in the
       // generateForUid parameter. All other meta data comes from a child element of this.
   
       IEnumerable<XElement> resultset =
       from el in DimodeloMetaDataXML.Element("Facts").Elements("Fct")
       where el.Element("Uid").Value == generateForUid
       select el;
   
       // there will only be one result in the result set.
       XElement factXML = null;
       foreach (XElement el in resultset)
           factXML = el;
   
       // Add the ExecSQL Command task to the package
       ExecSQL = new EzExecSqlTask(this);
   
       // Assign a name to the ExecSQL command from Dimodelo Meta Data
       ExecSQL.Name = "Select " + factXML.Element("Name").Value;
   
       //Return the resulting package back to the caller as a string
       return this.SaveToXML();
   
   }

4.  Next replace the Main method with the code below. You wouldn’t normally need this method, but it makes it easier to test the class. The Main method simply calls the Execute method and then saves the result to a file.

    static void Main(string[] args)
           {
   
               // This method emulates how Dimodelo Data Warehouse Studio calls the Execute method of
               // your EzAPI package generation class. Each Dimodelo Data Warehouse Studio project
               // contains a file called Dimodelo_Meta_Data.xml which contains your
               // Data Warehouse design data in XML format. To access the design data use
               // the following code, with the path to the relevant Dimodelo_Meta_Data.xml.
   
               DimodeloMetaData dmd = new DimodeloMetaData("Dimodelo_Meta_Data.xml");
   
               // Your ezAPI template will operate on only one Fact or Dimension at a time.
               // This is passed to the Execute method using the generateFor parameter.
               // The value of the generateFor parameter is the Uid of the Fact/Dimension in Dimodelo_Meta_Data.xml.
               // The GUID value below is a known Uid of the a Fact table in Dimodelo_Meta_Data.xml.
   
               String generateFor = "4c47c33a-562c-4d5c-9bf1-ef739cbf7ac1";
   
               // Execute the Hello World with Dimodelo Example
   
               HelloWorldEzAPIExampleWithDimodelo pkgGenerator = new HelloWorldEzAPIExampleWithDimodelo();
   
               string HelloWorldPkg = pkgGenerator.Execute(dmd, generateFor);
   
               System.IO.StreamWriter file = new System.IO.StreamWriter("HelloWorldDimodelo.dtsx");
               file.WriteLine(HelloWorldPkg);
               file.Close();
   
               System.Console.WriteLine("Hello World With Dimodelo Generation Complete");
   
           }

 Generate the SSIS package.

1. Build the project. It must build without bugs before you move on to the next step.
2. Set the start object of your project, so when you build and debug the project it runs your class’s Main method. Right click the project node in the Solution Explorer, and select Properties.
3. In the Startup Object dropdown select the class you just created HelloWorldEzAPIExampleWithDimodelo.
4. Save the Project properties.
5. Press Ctrl + F5 to build and run the Main method of your class.
6. Everything going well, there should now be a new SSIS package called HelloWorldDimodelo.dtsx in the project folder under binDebug.
7. Open the SSIS package in BIDS. It should contain a single EXEC Sql task with the name ‘Select Tran_Fact’.

Download the completed C# project here.