Hadi Fadlallah
Class hierarchy for the data flow engine in EzApi

Biml alternatives: Building SSIS packages programmatically using EzAPI

March 26, 2020 by

In the previously published article, Biml alternatives: Building SSIS packages programmatically using ManagedDTS, we talked about building SSIS packages using the managed object model of the SSIS engine (ManagedDTS). In this article, we will illustrate another Biml alternative, which is the EzApi class library, and we will make a comparison between both technologies.

What is the EzAPI class library?

As we talked previously, Microsoft provides a set of assemblies that allows users to programmatically create, manage and execute SSIS packages. The main weakness of using these assemblies is that they are very complex and hard to understand. For this reason, the Microsoft SSIS team developed a .Net Class library called EzApi that facilitates automating SSIS package development. This class library is developed using C# and it was used internally by the team for a while. Later it was published on CodePlex within the Integration Services community samples project and later, it was migrated to Git-Hub after the Code Plex website is closed, but this project is not improved for a while.

After SQL Server 2016 was released, Pedros Morais (a Full Stack developer) announced EzApi2016, a fork of the original project to adapt this library to the new SQL Server version. And later, this library is available on NuGet and it is improved periodically. More information about the improvement can be found on the Pedro Morais website.

This class library can be considered as an intermediate layer between the application and the SSIS managed object model. Each object in this library is mapped to the related COM object in SSIS. You can check the runtime and data flow engines class hierarchy in the following images (Reference: EzAPI – Alternative package creation API)

Class hierarchy for the runtime engine in EzApi

Figure 1 – Class hierarchy for the runtime engine

Class hierarchy for the data flow engine in EzApi

Figure 2 – Class hierarchy for the data flow engine

As shown in the runtime engine class hierarchy, the base entity is the executable, which can be a container or a task. And in the data flow engine, the base entity is a component which can be an adapter (source or destination – since they adapt SSIS to an external source) or a transformation.

Installation using NuGet

The easiest way to use this class library is to open the NuGet package manager console within Visual studio (Tools > NuGet Package Manager > Package Manager Console).

Openning Package Manager console

Figure 3 – Opening NuGet package manager console

Then you should use the following command (0.8.93 is the latest version right now):

Package manager console output

Figure 4 – Screenshot of the package manager console output

Now, the class library is added as project reference:

Showing EzApi class library within the project reference

Figure 5 – Class library added as a reference

If you are working offline, you can simply download and build the project locally, then add the generated class library as a reference within your project.

Building packages using EzApi

Before getting started, you should note that you may need to add a reference for the SSIS managed object model assemblies to the project since it is required by some of the methods in the class library.

Creating a new package

First of all, you should import the Microsoft.SqlServer.SSIS.EzAPI namespace within the current class:

To create a new package and save it within a local path you can use the following code:

Loading package from existing file

To load a package from an existing dtsx file, you can use the following code:

Adding Connection managers

In order to add a connection manager, you should use EzConnectionManager or the relevant connection manager class as following:

Adding tasks

To add a task within a container (package or container), you should use the task-related class and specify the parent container on the class initialization. For example:

Adding Data Flow Tasks

Using Ezapi, adding tasks is more easier than SSIS managed object model. The following code is used to create a package, adding an OLE DB connection manager, adding a data flow task with an OLE DB Source and destination in order to transfer data between [Person].[Person] and [Person].[Person_temp] tables:

Executing packages

To execute a package, you can simply use the Execute() method as follows:

Package templates

There are some template packages added within this class library. As example:

  • EzDataFlowPackage: a package that contains a data flow task
  • EzForLoopDFPackage: a package that contains a for loop with a data flow task
  • EzForLoopPackage: a package that contains a for loop container

These templates decrease the time and lines of codes needed to develop packages.

Online Resources

EzApi is not very popular, but there is some article online that you can refer to in order to learn more:

Comparing with Biml

Simplicity

After creating the package, we will convert it to a Biml script, as we explained in Converting SSIS packages to Biml scripts article

As you can see, Biml script is a bit simple that the C# code we wrote previously. We can say that both technologies used facilitates the SSIS package automation much more than using the traditional assemblies provided by Microsoft. And it depends on the user programming background, since developers will found themselves more familiar with EzAPI, while other employees or analyst will go Biml.

Context

As we mentioned in the previous article, if you need to automate package creation and management within an application, then you cannot go with Biml. In other cases, you are free to choose between both technologies.

Error handling in development

EzAPI has the same limitation than ManagedDTS since it doesn’t show a clear error message if encountered while building the package:

Meaningless Exception is thrown while building package programmatically using EzApi

Figure 6 – Meaningless exception thrown while building package programmatically

Online Resources

Even if Biml doesn’t have plenty of resources online, but they are much more than the one related to EzAPI since you may not find more articles than the ones we mentioned previously (even those articles take a lot of time to be recognized).

Conclusion

In this article, we have illustrated another alternative of Biml called EzAPI, we talked briefly about this library and why it was developed, then we explained how to use it to create and manage SSIS packages. Finally, we made a comparison between both technologies.

Table of contents

Getting started with Biml
Converting SSIS packages to Biml scripts
Using Biml scripts to generate SSIS packages
Extending Biml with C# scripts
Using external C# script files within Biml
Biml alternatives: Building SSIS packages programmatically using ManagedDTS
Biml alternatives: Building SSIS packages programmatically using EzAPI

Hadi Fadlallah
348 Views