Use EzAPI with an existing Package as a Template

This post shows you how to use EzAPI to load and modify an existing SSIS package. Create a template SSIS packages, and generate a suite of packages from the template.

As far as I can tell, this is the first time this really useful feature of EzAPI has been documented. Its always been hinted at in the comments of the original Microsoft SSIS team EzAPI blog post, but never explained.

After deciphering those comments and stepping through the EzAPI code with the debugger, I’ve worked out how to do this, and its relatively easy.

I’ve always wanted to be able to write a template SSIS package, and then combine that package with some design meta data to generate other packages. That is exactly what you can do with EzAPI.

There are two key things you need to do:

1. In every executable and component of the template SSIS package you need to put <EzName>name</EzName> in the description.
2. In the EzAPI class representing your package there must be a public property for every executable and component, and the property name must match the <EzName>. The type of the property must be the equivalent Ez… type also.

When you use EzPackage.LoadFromFile to load your template SSIS package, the existing executables and components are associated with their equivalent public property by name. This gives you a handle on the existing executables/components, and allows you to then manipulate them using code.

If none of this makes sense, then perhaps you need to first read our Getting started with EzAPI and EzAPI Hello World example.

An Example

Below is an example of this technique.

Preliminaries

The Template

I’ve created a simple template package. The Control flow contains a Exec SQL task and a Data Flow task:

The Data flow contains a Source, Destination and a Derived column component.

Within each executable/component the description property has had it’s value set to <EzName>name</EzName>. For example the SQLTemplate task has its description set to <EzName>SQLTemplate</EzName>:

Meta Data

The design Meta Data for this sample comes from a Dimodelo Data Warehouse Studio, our Data Warehouse Automation tool. We use EzAPI to generate all our SSIS packages, but don’t start with an existing package as a template (till now!). The Meta Data is a de-normalized version of the meta data captured in a Dimodelo Data Warehouse Studio project. Its denormalized to make it easier to use during generation, with many annotations that supply commonly used attributes. The Meta data is generated every time a Dimodelo Data Warehouse Studio project is generated, and is stored in a Dimodelo_Meta_Data.xml file in the project. See the sample below:

Dimodelo Data Warehouse Studio also supplies a .NET API to access the Meta Data programmatically. For example, to get a handle on the meta data file you would do something like this:

 // get access to a DimodeloMetaData 
 DimodeloMetaData dmd = new DimodeloMetaData("Dimodelo_Meta_Data.xml");
 XElement DimodeloMetaDataXML = dmd.getXElement();

It’s possible to get the meta data as just raw xml, with an xpathnavigator, or as serialized version of the xml. In this example we get an XElement, which let us use LINQ to query the meta data. For example, to get the meta data for a particular Fact table:

 IEnumerable<XElement> resultset = from el in DimodeloMetaDataXML.Element("Facts").Elements("Fct")
 where el.Element("Uid").Value == "47a25fa3-45c9-42d7-bdc5-82d969a2d8ac";
 select el;
 XElement factXML = resultset.FirstOrDefault();

The EzPackage Code

Below is the full listing for the class that modifies the template introducing meta data to manipulate the package, and returning a new package. How the Execute method of this class is called is discussed later. Expand below to see the source

[learn_more caption=”EzPackage Source Listing”]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.SSIS.EzAPI;
using Microsoft.SqlServer.Dts.Design;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Dimodelo.MetaData;
using System.Xml.Linq;
using System.Xml.XPath;
 
namespace EzAPISample
{
    class ExamplePackage : EzPackage
    {
 
        #region Constructors
 
        // Provide this constructor only if you want to overload Assignment operator
        public ExamplePackage(Package p) : base(p) { }
 
        public ExamplePackage() : base() { }
 
        // Provide assignment operator if you want to be able to Assign SSIS Package to EzPackage
        public static implicit operator ExamplePackage(Package p) { return new ExamplePackage(p); }
 
        #endregion
 
        #region Properties
        // All the tasks, components and connection managers which should automatically be
        // assigned when SSIS package is assigned to EzPackage MUST BE PUBLIC MEMBERS.
 
        public EzOleDbConnectionManager WarehouseCM;
        public EzExecSqlTask SQLTemplate;
        public EzDataFlow Dataflow;
        public EzOleDbSource Source;
        public EzOleDbDestination Destination;
        public EzDerivedColumn Derived;
 
        #endregion
 
        #region Methods
        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 resultset = from el in DimodeloMetaDataXML.Element("Facts").Elements("Fct")
                                                  where el.Element("Uid").Value == generateForUid
                                                  select el;
                XElement factXML = resultset.FirstOrDefault();
 
            // Modify ExecSqlTask SQLTemplate
                // Assign a name to the ExecSQL command from Dimodelo Meta Data
                SQLTemplate.Name = "Select " + factXML.Element("Name").Value;
                // Set the query
                SQLTemplate.SqlStatementSource = "Select * From " + factXML.Element("Long_Name").Value;
                // Set the source type
                SQLTemplate.SqlStatementSourceType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.SqlStatementSourceType.DirectInput;
 
            //Modify the Connection Manager
                // Retrieve the Warehouse connection meta data
                IEnumerable resultSet =
                from el in DimodeloMetaDataXML.Element("Connections").Elements("Connection")
                where el.Element("Name").Value == "Warehouse"
                select el;
 
                XElement connection = resultSet.FirstOrDefault();
 
                // set the properties of the ezSQLOleDbCM object
                WarehouseCM.Name = connection.Element("Name").Value;
                WarehouseCM.ConnectionString = connection.Element("OLEDB_Connection_String").Value;
 
            // Modify the DataFlow
                // Set the name of the Dataflow
                Dataflow.Name = "Transform " + factXML.Element("Name").Value;
 
            // Modify the Source Component
                Source.AccessMode = AccessMode.AM_OPENROWSET;
                Source.Table = factXML.Element("Long_Name").Value;
                //MUST Reconnect with connection
                Source.Connection = WarehouseCM;
 
            // Modify Derived Component
                // MUST reattach to source.
                Derived.AttachTo(Source, 0, 0);
                Derived.Expression["Batch_Effective_Date"] = "20150101";
 
            //Set the Destination Component
 
                Destination.ValidateExternalMetadata = true;
                Destination.AccessMode = AccessMode.AM_OPENROWSET;
                Destination.Table = factXML.Element("Long_Name").Value;
                //MUST Reconnect with connection
                Destination.Connection = WarehouseCM;
                ////MUST Reattach after Source and Destination are set up.
               Destination.AttachTo(Derived, 0, 0);
 
 
            //Return the resulting package back to the caller as a string
            return this.SaveToXML();
        }
        #endregion
    }
}

[/learn_more]

The important thing to note is there is a public property that correspond to each of the executables/components in the template package. The type of the property is the Ez… type equivalent of the executable/component type, and the name of the property exactly matches the content of the <EzName> element in the description property of the executable/component in the template package. When the LoadFromFile method is called on the class, EzAPI associates the existing executables/components in the template package with their matching property in the class.

 public EzOleDbConnectionManager WarehouseCM;
 public EzExecSqlTask SQLTemplate;
 public EzDataFlow Dataflow;
 public EzOleDbSource Source;
 public EzOleDbDestination Destination;
 public EzDerivedColumn Derived;

The execute method modifies the properties of the executables and components in the class, then returns the modified package XML to the caller using return this.SaveToXML();

1. Retrieves the Dimodelo Meta Data for the fact with a Uid = generateForUid parameter passed to the method.
2. Modifies the name of the ExecSQL task, and sets the SQL statement.
3. Retrieves the Data Warehouse connection string from the Meta Data, and modifies the WarehouseCM connection manager.
4. Renames the data flow component.
5. Sets the Source component table to the name of the fact table.
6. Adds a derived column.
7. Sets the Source component table to the name of the fact table.

Source and Destination components can be finicky. You need to call the methods of the Source/Destination in a particular order. The order in this example works. You should think of it as if you were taking each of the actions in the SSIS visual studio design UI. These are design time methods. There are also things you MUST do. These have a comment with the word MUST. So, for what ever reason, even though the components in the template package data flow are attached, you must call the AttachTo method of each component. You must also set the connection of each Source or Destination component, and you must do this before calling the AttachTo method.

Generating the Package

I used a small console class to execute EzPackage execute method. The source is below.

[learn_more caption=”Source code”]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml.XPath;
using Microsoft.SqlServer.SSIS.EzAPI;
using Microsoft.SqlServer.Dts.Design;
using Microsoft.SqlServer.Dts.Runtime;
using Dimodelo.MetaData;
 
namespace EzAPISample
{
    class Program
    {
        static void Main(string[] args)
        {
            //  get access to a DimodeloMetaData . 
            DimodeloMetaData dmd = new DimodeloMetaData("Dimodelo_Meta_Data.xml");
            XElement DimodeloMetaDataXML = dmd.getXElement();
 
            // generateFor parameter which is the Uid of the Fact/Dimension in Dimodelo_Meta_Data.xml.
            String generateFor = "47a25fa3-45c9-42d7-bdc5-82d969a2d8ac";
 
            //Instanciate the ExamplePackage class
            ExamplePackage examplePkg = new ExamplePackage();
 
            //Load the template SSIS package 'into' the Example package
            examplePkg.LoadFromFile(@"C:\Users\Adam\Documents\Visual Studio 2010\Projects\EzAPISample\EzAPITemplates\Template 1.dtsx");
 
            //Call the Execute message to transform the template into the new package
            string newPackageXML = examplePkg.Execute(dmd, generateFor);
 
            //Remove the old layout XML from the package file
            removeLayoutInformation(newPackageXML);
 
            //Save the new package to Output.dtsx
            System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\Adam\Documents\Visual Studio 2010\Projects\EzAPISample\EzAPITemplates\Output.dtsx");
            file.WriteLine(newPackageXML);
            file.Close();
        }
 
        public static string removeLayoutInformation(string strXML)
        {
            try
            {
                //Remove the layout information.
                while (strXML.IndexOf("") > -1)
                {
                    strXML = strXML.Remove(strXML.IndexOf(""), strXML.IndexOf("") - strXML.IndexOf("") + 22);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return strXML;
        }
    }
}

[/learn_more]
The code is fairly self explanatory, the main points are:

1. The LoadFromFile method is used to load the template package into the examplePkg object. This is where the relationship between the template packages’s executable/components is made with the public properties of the class.
2. The Execute method of the examplePkg object returns the transformed package XML.
3. It’s necessary to strip out the diagram layout information that was in the original template package. Its not required for package execution, and DDST/BIDS will generate new layout if you open the package.  The removeLayoutInformation method was written by Josh Robinson, you can find out more here.

Personally I think this is a very powerful way to automate your ETL generation. However this takes a lot of commitment and potentially 100’s of hours. If you are looking for a turn key method of Data Warehouse Automation, then you should consider our Data Warehouse Automation tool Dimodelo Data Warehouse Studio. Out of the box, you can capture design meta data, generate SSIS packages and database synchronization code, deploy the solution to multiple environments and execute the ETL batches.