Hadi Fadlallah
The control flow of the package created using ManagedDTS

Biml alternatives: Building SSIS packages programmatically using ManagedDTS

March 25, 2020 by

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)

SSIS architecture

Figure 1 – SSIS architecture

We can note that the main SSIS components are:

  1. Integration Services Service: It is a windows service that monitors running packages, it also manages the package storage within SQL Server
  2. 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
  3. 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:

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

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:

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:

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:

  1. 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:


  2. 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:


  3. 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:


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]:

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 :

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

The control flow of the package created using ManagedDTS

Figure 2 – Created package control flow

The data flow task of the package created using ManagedDTS

Figure 3 – Created data flow task

Executing package programmatically

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

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.

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.

Unclear exception while building package using ManagedDTS

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.

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
Building databases using BIML

Hadi Fadlallah
1,861 Views