Biml alternatives: Building SSIS packages programmatically using ManagedDTS

In the previously published articles in this series, we have explained how to use Biml to create and manage SQL Server Integration Services (SSIS) packages. In this article, we will talk about the first alternative of this markup language which is the Integration Services managed object model (ManagedDTS and related assemblies) provided by Microsoft.

In this article, we will first illustrate how to create, save and execute SSIS packages using ManagedDTS in C#, then we will do a small comparison with Biml.

Integration Services object model

To explain the integration services object model, first we have to take a look at the SSIS architecture. As shown in the figure below (Reference: Integration Services Programming Overview)

Figure 1 – SSIS architecture

We can note that the main SSIS components are:

Integration Services Service: It is a windows service that monitors running packages, it also manages the package storage within SQL Server
Integration Services Runtime Engine: It controls the management and execution of packages, by implementing the infrastructure that enables execution order, logging, variables, and event handling
The Data Flow Engine: It manages the data flow tasks within a package since this task is specialized to move data between different sources and destinations and it can contain additional components

As shown in the architecture, both the runtime and data flow engine have an object model that allows them to be fully managed from custom applications, SSIS designer, or SQL Server Import and Export wizard. Besides, they are both written in native code and can be accessed using command-line utilities (DTExec) or custom applications.

The object model allows developers to develop and manages SSIS packages using .NET-compliant languages.

SSIS packages development using the managed object model

SSIS development assemblies

The following assemblies are the one that you may need to develop, manage and execute SSIS packages from .NET applications:

Name	Qualified Name	Description
ManagedDTS	Microsoft.SqlServer.ManagedDTS.dll	Managed runtime engine
PipelineHost	Microsoft.SqlServer.PipelineHost.dll	Managed data flow engine
DTSRuntimeWrap	Microsoft.SqlServer.DTSRuntimeWrap.dll	Wrapper for the native runtime engine
DTSPipelineWrap	Microsoft.SqlServer.DTSPipelineWrap.dll	Wrapper for native data flow engine

Before SQL Server 2019, these assemblies were located in “<drive>:\Program Files\Microsoft SQL Server\<SQL Version>\SDK\Assemblies” path. In SQL Server 2019, they are located in the global assembly cache with the .NET framework assemblies.

Creating a new package using the object model

To create an SSIS package from your .Net code, you have to add the ManagedDTS assembly as a reference in your project. Then you should import the Microsoft.SqlServer.Dts.Runtime assembly as the following:

1	using Microsoft.SqlServer.Dts.Runtime;

The following code creates an empty package and saves it as .dtsx file:

Application app = new Application();

Package p = new Package();

app.SaveToXml(@"D:\test.dtsx",p,null);

Note that you can save the package to SQL Server (Msdb database) using SaveToSqlServer() and SaveToSqlServerAs() methods, or you can save it to an SSIS package store using SaveToDtsServer() method. For more information, you can refer to: Saving a Package Programmatically.

Loading package from an existing file

To load an existing package, you can use the Application.LoadPackage() method as following:

1 2	Application app = new Application(); Package p = app.LoadPackage(@"D:\test.dtsx", null);

Adding Connection managers

To add a connection manager, you must use the Application.ConnectionManager class, then you should specify the connection type and the needed information based on this type. As an example, the following code adds an OLE DB connection manager:

string ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;";

ConnectionManager DatabaseConnectionManager = p.Connections.Add("OLEDB");

DatabaseConnectionManager.ConnectionString = ConnectionString;

DatabaseConnectionManager.Name = "ConnectionOLEDB";

DatabaseConnectionManager.Description = "SSIS Connection Manager for OLEDB";

Adding Tasks

Using ManagedDTS, tasks are added within Executables property in the Package class. There are different approaches to add a task within the package control flow:

Adding the task explicitly by providing the assembly information manually

In this approach, you have to enter the assembly name, qualified name, version, culture, and the public key token into the Add() method. For example:

1 2	Package p = new Package(); p.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.000.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");

Using the AssemblyQualifiedName

In this approach, you must add the assembly related to the task as a reference and import it in your code, then you should retrieve the assembly qualified name of the task’s related class (located in “<drive>:\Program Files\Microsoft SQL Server\<SQL version>\DTS\Tasks”). As an example, if you need to add an Execute SQL Task, you should first add Microsoft.SqlServer.SQLTask.dll as reference. Then you should use the following code:

using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

…

p.Executables.Add(typeof(Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask).AssemblyQualifiedName);

Using STOCK moniker

You can refer to the following documentation for a full list of tasks’ stock monikers. Then you should use it as the following:

1
2
3

using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
…
p.Executables.Add("STOCK:SQLTask");

Adding a Data Flow Task

As we mentioned above, the data flow task is a special SSIS task where we can add components. For this reason, we will talk about it in a separate section. As we mentioned in the first section, You should add PipelineHost and DTSPipelineWrap assemblies besides the ManagedDTS to work with the data flow engine. The following code is to create a package, add an OLE DB connection manager, add a Data Flow Task that contains an OLE DB Source and OLE DB destination and configure them to import data from [Person].[Person] table into [Person].[Person_temp]:

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

…

Executable e = p.Executables.Add("STOCK:PipelineTask");

TaskHost thMainPipe = (TaskHost)e;

MainPipe dataFlowTask = (MainPipe)thMainPipe.InnerObject;

Adding Data Flow Tasks components

You can add data flow task components using their Creation Name or the assembly qualified name. The second approach requires importing the component related assembly (located in “<drive>:\Program Files\Microsoft SQL Server\<SQL version>\DTS\PipelineComponents”). The following example illustrates how to add an OLE DB Source and destination components and create a mapping between them :

//Creating a new package

Application app = new Application();

Package p = new Package();

//Adding the connection manager

ConnectionManager DatabaseConnectionManager = p.Connections.Add("OLEDB");

DatabaseConnectionManager.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;"; ;

DatabaseConnectionManager.Name = "ConnectionOLEDB";

DatabaseConnectionManager.Description = "SSIS Connection Manager for OLEDB";

//Adding the data flow task

Executable e = p.Executables.Add("STOCK:PipelineTask");

TaskHost thMainPipe = (TaskHost)e;

MainPipe dataFlowTask = (MainPipe)thMainPipe.InnerObject;

thMainPipe.Name = "Import Person";

IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();

component.Name = "OLEDBSource";

component.ComponentClassID = app.PipelineComponentInfos["OLE DB Source"].CreationName;

//Get the design-time instance of the component.

CManagedComponentWrapper instance = component.Instantiate();

//Initialize the component.

instance.ProvideComponentProperties();

//Map the component to a connection manager

component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(p.Connections[0]);

component.RuntimeConnectionCollection[0].ConnectionManagerID = DatabaseConnectionManager.ID;

//Set the OLE DB Source properties

instance.SetComponentProperty("AccessMode", 2);

instance.SetComponentProperty("SqlCommand", "Select * from Person.Person");

// Reinitialize the metadata.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

// Create the destination component.

IDTSComponentMetaData100 destination = dataFlowTask.ComponentMetaDataCollection.New();

destination.ComponentClassID = app.PipelineComponentInfos["OLE DB Destination"].CreationName;

var destinationInstance = destination.Instantiate();

destinationInstance.ProvideComponentProperties();

destination.RuntimeConnectionCollection[0].ConnectionManagerID = DatabaseConnectionManager.ID;

destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(p.Connections[0]);

destinationInstance.SetComponentProperty("AccessMode", 3);

destinationInstance.SetComponentProperty("OpenRowset", "[Person].[Person_temp]");

// Create the path.

IDTSPath100 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(component.OutputCollection[0], destination.InputCollection[0]);

//Get the destination's default input and virtual input.

IDTSInput100 input = destination.InputCollection[0];

IDTSVirtualInput100 vInput = input.GetVirtualInput();

IDTSVirtualInputColumnCollection100 vInputColumns = vInput.VirtualInputColumnCollection;

destinationInstance.AcquireConnections(null);

destinationInstance.ReinitializeMetaData();

destinationInstance.ReleaseConnections();

//Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn100 vColumn in vInputColumns)

{

var inputColumn = destinationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

var externalColumn = input.ExternalMetadataColumnCollection[inputColumn.Name];

destinationInstance.MapInputColumn(input.ID, inputColumn.ID, externalColumn.ID);

}

app.SaveToXml(@"D:\test.dtsx",p,null);

After executing the application, the package is created successfully. The following screenshots show the package Control Flow and Data Flow Task:

Figure 2 – Created package control flow

Figure 3 – Created data flow task

Executing package programmatically

After creating a package, we can execute it using the Execute() method as following:

Package p = new Package();

...

DTSExecResult result = p.Execute();

Online Resources

In the previous sections, we briefly explained how to create and execute packages using ManagedDTS, but there are many more instructions you need to know such as mapping tasks, configuring task properties, error handling … For this reason, Microsoft has provided helpful documentation where most of these operations are explained; Integration Services Developer Documentation.

Comparing with Biml

Simplicity

After creating the package using ManagedDTS, we will convert it to a Biml script as we explained in Converting SSIS packages to Biml scripts. You can check how Biml replaces a complex C# script and facilitate the reading and editing process since it is simpler and does not require programming knowledge.

</Connections>

<Tasks>

<DirectInput>Select * from Person.Person</DirectInput>

</OleDbSource>

</OleDbDestination>

</Transformations>

</Dataflow>

</Tasks>

</Package>

</Packages>

</Biml>

Context

On the other side, it is more preferable to use ManagedDTS when creating and executing the packages is done within an application and it is related to other tasks since Biml is managed within an SSIS solution and it is not integrated within an application.

Error handling in development

Handling errors is a disaster while using ManagedDTS since it does not show a clear error message as shown in the image below. While in Biml, the “Check Biml for Errors” tool return all errors encountered while validating the script.

Figure 4 – Threw exception while building SSIS package programmatically

Resources

There is a lack of related articles and guides for both technologies. But the ability to Convert SSIS packages to Biml scripts makes it easier to learn since it simplifies the learning process in case you are familiar with SSIS.

Conclusion

In this article, we have explained one of the alternatives for creating SSIS packages using Biml which is the managed object model of the integration service runtime engine. Additionally, we made a small comparison between both approaches to illustrate the main difference.

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

Building databases using BIML

Building SSAS OLAP cubes using Biml

Building SSAS tabular models using Biml

Author
Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

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

Latest posts by Hadi Fadlallah (see all)

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

SQLShack

Biml alternatives: Building SSIS packages programmatically using ManagedDTS

Integration Services object model