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

      1 2 3 4 5 6 7 8 9 10 11 12

      CREATE TABLE [dbo].[Person](   [BusinessEntityID] [int] NOT NULL,   [PersonType] [nchar](2) NOT NULL,   [NameStyle] [nvarchar](50) NOT NULL,   [Title] [nvarchar](8) NULL,   [FirstName] [nvarchar](50) NOT NULL,   [MiddleName] [nvarchar](50) NULL,   [LastName] [nvarchar](50) NOT NULL,   [Suffix] [nvarchar](10) NULL,   [EmailPromotion] [int] NOT NULL,   [ModifiedBy] [nvarchar](50) NOT NULL,   [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY]

   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]”)

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

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

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:

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

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:

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

• 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