EzAPI – a C# library for creating SSIS packages programatically

What is EzAPI?

EzAPI is a .NET library written in C#, that abstracts away a lot of the cumbersome low-level coding needed to create SSIS packages XML directly in a programming language. Without EzAPI, one would have to directly reference and use the Microsoft.SqlServer.Dts libraries, which are quite difficult to work with and poorly documented.

EzAPI was created by a member of the Microsoft SSIS team, to make internal testing of SSIS functionality easier. Although not an official Microsoft release, EzAPI is still supported and actively developed on CodePlex, where you can also find the C# source code.

EzAPI supports SQL Server 2008 and SQL Server 2008 R2 but has also been updated for SQL Server 2012. Using the latter version of EzAPI requires a little more effort, as there’s not currently an installer package available, which means you’ll have to download and compile the EzAPI source code yourself.

To make things a little easier to work with, I’ve taken the liberty to prepare some all-in-one solutions for Visual Studio 2010 and Visual Studio 2012, that you can use as templates for your own work. These solutions have been tested with SQL Server 2008 R2 and SQL Server 2012. Please find the files you need below.

NOTE: The minimum requirement for using these EzAPI project solutions is an edition of the Visual Studio IDE that allows you to compile and build C# projects. Business Intelligence Development Studio (BIDS) from SQL Server 2008 or SQL Server Data Tools (SSDT) from SQL Server 2012 are not sufficient. If you prefer the 2010-version of the IDE, you’ll need Visual C# 2010 Express. For the 2012-version, you’ll need Visual Studio Express 2012 For Windows Desktop. Both can be downloaded for free from here.

Furthermore, you will need to have the Integration Services feature from SQL Server 2008 R2 or SQL Server 2012 installed on the machine you plan to develop your EzAPI solution on. A Standard- or Developer Edition of SQL Server will suffice (but Express Edition will not), and you do not need anything but the Integration Services feature, although it is typically practical to also have the Database Engine feature available on your development machine.

EzAPI all-in-one solutions and extensions

EzAPI is a great library that makes a lot of difficult tasks very easy. It gives you a lot of flexibility and encourages generating SSIS packages from metadata, which is very valuable in larger BI projects. However, after working with the library for a couple of days, I came up with a few additions to the library, to make things even easier. Thanks to the power of the object-oriented C# programming language (extension methods, for the win!), I was able to incorporate my extensions to EzAPI in a separate library, without having to touch the source code of EzAPI – thus, theoretically, my stuff should keep working, whenever EzAPI gets updated.

Anyway, here’s the solutions mentioned above. These are .zip files containing the Visual Studio solution and project files. You’ll find two solutions called “EzAPI SSIS template” and “EzAPI SSIS template with extensions” respectively, so you can choose whether you want my additions in your project or not. The latter includes the source code and documentation for my EzAPI extensions.

Download EzAPI project template for SQL2008 and SQL2008R2 (.zip, 44 kB)
Download EzAPI project template for SQL2012 (.zip, 49 kB)
Download EzAPI project template with entensions for SQL2012 (.zip, 63 kB)

Other ressources

I gave a short presentation under the topic of programmatically generating SSIS packages using BIML and EzAPI, at a MS-BIP Meeting in Copenhagen on september 5th, 2013. The slides and samples presented, are also available here:

Download samples (.zip, 61 kb)

Using EzAPI

Since EzAPI is a standard .NET library, you can use it with any .NET compatible programming language. In this introductory tutorial, I will assume we are using C#, but feel free to try out EzAPI in any language of your choice.

Step 1) Creating the Visual Studio project

If you want to start from scratch, create a new C# Console Application in Visual Studio. Add a project reference to EzAPI.DLL and (optionally) EzAPI.Extensions.DLL. These files may be found in the \bin\-folder of the project templates available for download above. Alternatively, you can download the EzAPI MSI installer from CodePlex (for SQL Server 2008 R2), or download the updated EzAPI source code (for SQL Server 2012) and compile it yourself.

…or, you could just open one of the project templates above, and continue directly to step 2.

Step 2) Writing some code

In your Console Application, open the Program.cs file, add the following using-directive at the top of the file:

using Microsoft.SqlServer.SSIS.EzAPI;

Add the following code to the Program-class’ Main() method:

static void Main(string[] args)
{
// Create package:
var myPackage = new EzPackage() { Name = "MyFirstPackage" };

// Save package:
myPackage.SaveToFile("MyFirstPackage.dtsx");
}

Step 3) Run your code

Hit F5 to start Visual Studio debugging. You should see your application console window opening and closing again after a few seconds. Now, go to the output folder of your project, this is typically the \bin\Debug\ folder inside your project folder. You should see a file called “MyFirstPackage.dtsx”. Open the file in BIDS/SSDT. Congratulations! This is your first SSIS package generated with EzAPI, but as this package is empty, it is not really of much use.