SQL Server Integration Services packages automation tools
Even after the rise of Big Data technologies, Microsoft SQL Server Integration Services still one of the most popular data integration tools. Mainly, SSIS developers use Visual Studio to develop their data integration packages. One of the main challenges that face the SSIS developers is that they design tens of hundreds of similar packages, where they need to recreate the package from scratch each time. Even if in SQL Server 2016 SSIS package parts were introduced to increase the reusability, many scenarios still require a higher level.
This article will mention five approaches that I have tried while working as an ETL developer to automate building SSIS packages.
1. Integration Services Object Model (ManagedDTS and related assemblies)
The first approach for SSIS package creation automation is the Integration Services Object Model, a group of Microsoft assemblies, allowing .NET developers to build SSIS packages programmatically. These assemblies are installed within the SSIS installation.
Even if the idea of building SSIS packages programmatically looks attractive, but once you tried building your SSIS package using the object model, you may not do so again since it is hard to develop and hard to debug.
Suppose you are interested to learn more about this approach. In that case, you can check my published article on SQL Shack, where I provided a working example of how to create a basic package.
2. EzApi
Since the object model assemblies are complex, the Microsoft SSIS team developed EzAPI. EzAPI is a C# class library built on the top of the object model assemblies. It contains a set of a predefined set of package and components templates. EzAPI was first used internally by the SSIS team and later was published on CodePlex and later migrated to GitHub.
EzAPI is much easier than the SSIS object model assemblies, but they don’t guarantee the same automation level. For example, when adding a task or component to the package, you should use task-specific class in EzAPI. In ManagedDTS, you need to use an abstract SSIS Task class and pass the task type as a string. The following example shows the difference between adding an OLE DB Source component using both technologies:
EzAPI:
EzOleDbSource source = new EzOleDbSource(dataflow);ManagedDTS:
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();component.Name = “OLEDBSource”;
If you are interested to learn more about EzAPI, you can refer to the following article previously published on SQL Shack.
3. Business Intelligence Markup Language (BIML)
BIML is an XML dialect used to create and configure Microsoft SQL Server Integration Services packages and Analysis Services projects.
This language was released for the first time in 2009 by Varigence, a company founded by former Microsoft employee Scott Currie.
Varigence developed several BIML products where BimlExpress is the most popular. BimlExpress is a Visual Studio extension that allows you to write Biml script and convert then into SSIS packages.
Being simpler than ManagedDTS and EzAPI, and since it doesn’t require any programming experience, BIML is more preferable for non-developers.
You can check the following series published previously on SQL Shack to get started with BIML.
4. ETLGen
ETLGen is an Azure Data Factory (ADF) and SSIS commercial automation tool developed by Hawkers Technologies. ETL is a C# WinForms application built on the top of the Integration Services object model assemblies.
For a while, I tried ETLGen (90 days trial). There was a great effort made while developing this application. In brief, I will try to mention some of its strengths and weak points.
The most significant part of this application is converting an SQL query into an SSIS package. Also, it will enable building packages based on user-developed SSIS package templates.
This application’s main limitation is the poor user interface, and it can be used only in some scenarios. Besides, It can run only with SSIS 2016.
For the latest updates, you can follow @ETLGen on Twitter.
5. Pegasus
Pegasus is a C# class library developed by Sam Kolli. It is used to automate SSIS package creation. It contains several Wrappers that facilitates the SSIS tasks and components configuration instead of using the SSIS object model API directly.
Pegasus is an open-source project available on GitHub.
Till now, only basic task and components are supported. And unfortunately this project is not updated years ago. There are a forked version of this project available where some KingswaySoft components are added.
