First… What is EzAPI? EzAPI is an API released by the Microsoft SSIS Team which makes it easy to generate SSIS packages programmatically. EzAPI is the API that Dimodelo Architect uses to generate SSIS packages. EzAPI works for both SQL Server 2008 and SQL Server 2012 and is maintained via the Microsoft SQL Server Community Samples: Integration Services Codeplex project. The best source of documentation can be found in the EzAPI – Alternative package creation API blog post on the SSIS team blog. There have also been a couple of updates to the API over time, including support for SQL Server 2012. EzAPI was written by Evgeny Koblov, one of the testers on the SSIS team. The project, at the time of writing, is being maintained/administered by Matt Masson.

Installation

To install EzAPI for SQL Server 2008 use the installer available on codeplex. The installer installs both the EzAPI dll and some sample projects. The installer will place source and project files into a chosen directory. Additionally,binaries are placed into the Global Assembly Cache (GAC) and into the C:Program FilesMicrosoft SQL Server100DTSBinn folder. There are a number of prerequisites described as:

  • The sample must be installed on the local hard drive
  • You must install the common tools for SQL Server 2008.
  • You must install SQL Server Integration Services 2008.
  • Adventure Works sample database installed under a default instance on the local machine.

For SQL Server 2012 there is no installer released, so you will need to build the code available via the EzAPI – Package Generation API (SQL 2012) releaseof the codeplex project. You will need to checkout the associated Change Set: 86517.

Creating a new project that uses EzAPI

Create a new C# Console project.

  1. Open Microsoft Visual Studio 2010 (or 2008, but these instructions relate to VS 2010).
  2. Select File>New>Project.
  3. Select a the C# Console Application project.
  4. Name the project, determine a location for the project and click OK. The new project opens in Visual Studio.
  5. Right click the Reference folder and click Add Reference.
  6. Click the .NET tab. Select Microsoft.SqlServer.SSIS.EzAPI and click OK. If you can’t find it, then use the Browse tab to browse to the  C:Program FilesMicrosoft SQL Server100DTSBinn folder and select EzAPI.dll.
  7. You will also need to create references for the following namespaces.
    Microsoft.SqlServer.Dts.Design;
    Microsoft.SqlServer.Dts.Runtime;

I’m no C# .NET expert, so I don’t know the intricacies of Assemblies, the Global Assembly Cache and Assembly versions, but for me, these assemblies don’t appear under the Add References>.NET tab. I found the appropriate Assemblies (Microsoft.SqlServer.Dts.Design and Microsoft.SqlServer.ManagedDTS) at C:Windowsassembly. Add these as references.

You are now ready to create a new EzAPI C# class that generates an SSIS package.

Samples and Examples

Over time, we will be adding sample and examples of how to generate SSIS packages using EzAPI. All samples and Examples are available here.

Adam Gilmore is the Data Warehousing and Business Intelligence Principal Consultant for Dimodelo Solutions, creators of 'Dimodelo Architect' a Data Warehouse design and generation tool for the Microsoft platform.

1 Response

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>