Hadi Fadlallah
Create SSIS package control flow screenshot

Converting SSIS packages to Biml scripts

March 13, 2020 by

In our previous article, Getting started with Biml, we have explained what Biml is, what are the related tools and resources and how to get started with this technology. In this article, we will explain how to generate scripts from existing SSIS packages by showing all related options. Then, we will analyze the generated script and identify how each object is mentioned in the script.

Building SSIS package

To run our experiment, we will first build manually an SSIS package that creates a SQL table, read data from a flat-file – where the path is entered as a parameter – and insert it into the created table after adding two derived columns that contain the current date-time and a text value from an SSIS variable.

The SSIS package should contain the following objects:

  1. Connection Managers:
    1. Flat File connection manager (Connection string property is evaluated as an expression)
    2. OLE DB connection manager
  2. Tasks:
    1. Execute SQL Task (Execute the command below):

    2. Data Flow Task (Delay Validation = True):
      1. Flat File Source
      2. Derived Column Transformation (Contains two column expressions)
      3. OLE DB Destination
  3. Parameters:
    1. FlatFilePath (Type = String, Required = True)
  4. Variables:
    1. Username (Type = String, Value = “Admin”)
    2. DestinationTable (Type = String, Value= “[dbo].[Person]”)

Create SSIS package control flow screenshot

Figure 1 – Package overview

Convert SSIS Package to Biml

In order to convert an SSIS package into a business intelligence markup language script, you should go to the “Solution Explorer” tab, click on the package and press on “Convert SSIS package to BIML” as shown in the image below (note that BimlExpress should be installed):

Converting SSIS package to Biml

Figure 2 – Convert SSIS package option in the context menu strip

After you click on this button, the following form is opened:

BimlExpress Import packages tool

Figure 3 – Convert SSIS package form

As you can see, there are many options that we should specify in this form:

  1. SSIS Asset Location: where we should select where SSIS package is located, there are four types of location available:
    1. DTProj from File System: The *.dtproj file contains information about project configurations and items such as packages
    2. ISPAC from File System: The *.ISPAC file is a project deployment file
    3. DTSX from File System: The *.dtsx file is an SSIS standalone package file (Note that in our case, this option is selected by default as shown above)
    4. Project from Package catalog: This option is to select a deployed project to an SSISDB database
  2. Location: This control is to specify the location of the SSIS Asset; it depends on the type of location selected:
    1. If the location type is a DTProj or ISPAC file, it shows a textbox where the user should select the file path
    2. If the location type is DTSX file, then it shows a grid where file paths must be added (Note that the file path of the selected package is added by default as shown in the image above)
    3. If Project from the package catalog is selected, you should specify the connection parameters
  3. Import Options: This part contains some configuration related to the BIML script that will be generated; they are classified into three categories:
    1. SSIS Properties to include: where we should select the properties that we need to include within the generated script such as:
      1. SSIS Names
      2. SSIS ID’s
      3. SSIS Annotations
      4. Package creation metadata
    2. Merging Into Project: this part contains some project-level configuration such as:
      1. Import duplicate items
      2. Source Package Annotation
      3. Unmapped Column Annotation
    3. SSIS Version settings: This part contains the following option only:
      1. Auto Detect SSIS Version

In this example, we will not select any of the “SSIS Properties to include” and “Merging Into Project” options, since we are looking to generate a script of a single package object only, and we will leave “Auto Detect SSIS Version” option selected. After finishing configuration, we must click on the “Import” button which will detect the main objects included in this package as shown in the following image:

BimlExpress Import packages tool showing package assets

Figure 4 – Imported package assets

We can reconfigure the import task and click on “Reimport”, or we can select the assets that we need to include within the script from the “Imported Assets” tree view and click on “Add To Project” button to generate the Biml script and add it within the current solution.

Generated Biml script within the solution

Figure 5 – BimlScript1 file added to the solution

Generated Biml Script

In the following section, we will try to analyze the generated Biml script and to illustrate all the objects mentioned, if we open “BimlScript1.biml” file we can see the following script:

We can note that there are three main nodes within the scripts:

  1. Connections: In this node, all created connection managers are defined
  2. Package: In this node, all variables, parameters, tasks and components defined within the SSIS package are defined
  3. FileFormats: This node is related to the connections node since it contains all data structure information related to the external files connection managers (such a flat files, since all columns metadata redefined within the connection manager)

The following table contains the XML tag related for each object:

Object Type

XML Tag

OLE DB Connection manager

<Connection />

Flat File Connection manager

<FlatFileConnection />

Execute SQL Task

<ExecuteSQL></ExecuteSQL>

Data Flow Task

<DataFlow></DataFlow>

Flat File Source

<FlatFileSource></FlatFileSource>

Derived Column Transformation

<DerivedColumns></DerivedColumns>

OLE DB Destination

<OleDbDestination></OleDbDestination>

Variable

<Variable></Variable>

Parameter

<Parameter></Parameter>


You can refer to the script above to check the node hierarchy and how tasks and components are linked together.

Conclusion

In this article, we illustrated the BimlExpress Import packages tool and how we can use it to convert existing SSIS projects or standalone packages into Biml scripts. In my opinion, using this tool can be the best way to learn this markup language since it lets you convert SSIS packages and analyze the generated script to see how each object was serialized.

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
506 Views