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)
Figure 1 – Class hierarchy for the runtime engine
Figure 2 – Class hierarchy for the data flow engine
As shown in the runtime engine class hierarchy, the base entity is executable, which can be a container or a task. And in the data flow engine, the base entity is a component that 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).
Figure 3 – Opening NuGet package manager console
Then you should use the following command (0.8.93 is the latest version right now):
|
1 |
Install-Package EzApi -Version 0.8.93 |
Figure 4 – Screenshot of the package manager console output
Now, the class library is added as 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:
|
1 |
using Microsoft.SqlServer.SSIS.EzAPI; |
To create a new package and save it within a local path you can use the following code:
|
1 2 3 |
EzPackage ezPackage = new EzPackage(); ezPackage.Name = "EzAPIPackage"; ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx"); |
Loading package from existing file
To load a package from an existing dtsx file, you can use the following code:
|
1 2 3 |
EzPackage ezPackage = null; ezPackage = new EzPackage(); ezPackage.LoadFromFile(@"D:\EzAPIPackage.dtsx"); |
Adding Connection managers
In order to add a connection manager, you should use EzConnectionManager or the relevant connection manager class as following:
|
1 2 3 |
EzOleDbConnectionManager EzOleDbConMgr = new EzOleDbConnectionManager(ezPackage); EzOleDbConMgr.Name = "SourceOLEDBConnection"; EzOleDbConMgr.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;"; |
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:
|
1 2 3 4 5 |
EzExecSqlTask SQLTask = new EzExecSqlTask(ezPackage); SQLTask.Connection = EzOleDbCon; SQLTask.SqlStatementSourceType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.SqlStatementSourceType.DirectInput; SQLTask.SqlStatementSource = "SELECT * FROM Person.Person"; ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx"); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
//Creating package EzPackage ezPackage = null; ezPackage = new EzPackage(); ezPackage.Name = "EzAPIPackage"; //Adding an OLE DB connection manager EzOleDbConnectionManager EzOleDbConMgr = new EzOleDbConnectionManager(ezPackage); EzOleDbConMgr.Name = "SourceOLEDBConnection"; EzOleDbConMgr.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;"; //Adding a data flow task EzDataFlow dataflow = new EzDataFlow(ezPackage); //Adding an OLE DB Source EzOleDbSource source = new EzOleDbSource(dataflow); source.Connection = EzOleDbConMgr; source.Table = "[Person].[Person]"; source.AccessMode = AccessMode.AM_OPENROWSET; //Adding an OLE DB Destination EzOleDbDestination destination = new EzOleDbDestination(dataflow); destination.Connection = EzOleDbConMgr; destination.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD; destination.Table = "[Person].[Person_temp]"; //Linking source and destination destination.AttachTo(source); destination.LinkAllInputsToOutputs(); //Saving package to a dtsx file ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx"); |
Executing packages
To execute a package, you can simply use the Execute() method as follows:
|
1 |
ezPackage.Execute(); |
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:
- Microsoft tech community website
- Archived Microsoft Blogs – Paul Rizza’s T-SQL, SSIS and SSRS Blog
- Archived Microsoft Blogs – SSIS 2012 EzAPI – First Real Package
- Article published by Bill Fellows on his blog
- Article published on Dimodelo solutions website
- Article published by Kapacity business analytics website
- Related question on Stackoverflow
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <Connection Name="SourceOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;" /> </Connections> <Packages> <Package Name="{F9205928-FD01-4C5C-B444-52424DB72B88}" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithUserKey"> <Tasks> <Dataflow Name="Import Person"> <Transformations> <OleDbSource Name="OLE DB Source" ConnectionName="ConnectionOLEDB"> <DirectInput>Select * from Person.Person</DirectInput> </OleDbSource> <OleDbDestination Name="OLE DB Destination" ConnectionName="ConnectionOLEDB"> <ExternalTableOutput Table="[Person].[Person_temp]" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Package> </Packages> </Biml> |
As you can see, the Biml script is a bit simple than the C# code we wrote previously. We can say that both technologies used facilitate 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 analysts 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:
Figure 6 – Meaningless exception thrown while building package programmatically
Online Resources
Even if the 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
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023