In the previous article, Converting SSIS packages to Biml scripts, we explained how to convert existing SSIS packages into Biml scripts using Import Packages tool and we mentioned that this could be an efficient way to learn this markup language since it lets the user compare between the well-known SSIS objects found in the package and the correspondent elements in the generated scripts.

In this article, we will explain how to generate SSIS packages using business intelligence markup language by providing a step-by-step guide where we will illustrate how to configure each object and how to use variables, expressions and parameters within the package.

Desired output

We will build an SSIS package that read a directory path from parameter, create a destination SQL table, loop over flat files (*.csv) within the directory and import them to the created table after adding two derived columns; one contains username (value stored within SSIS variable) and the other contains the current date and time. To do that, we have to add the following objects:

1. Connection managers:
   1. Flat File Connection manager: where connection string must be dynamic to be able to read file paths enumerated by Foreach Loop container
   2. OLE DB Connection manager: where destination table must be created
2. Tasks:

   1. Execute SQL Task: To create destination SQL table using the following command:

      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. Foreach Loop container: To loop over flat files within the source directory
   3. Data Flow Task: Placed within the Foreach Loop container and it contains the following components:
      1. Flat File Source: Read from the Flat File connection manager
      2. Derived Column Transformation: Add two desired derived columns
      3. OLE DB Destination: The destination table is the created SQL table using the Execute SQL Task
3. Parameters:
   1. SourceDirectory (Type = string, Required = True)
4. Variables:
   1. DestinationTable (Type = string, Value = “[dbo].[Person]”)
   2. Username (Type = string, Value = “admin”)
   3. SourceFlatFile (Type = string): This variable will be used by Foreach Loop container to enumerate flat files

Writing a BIML script

In our previous article, we have noted that there are three parts within the script:

1. Connections: where connection managers are defined
2. Packages: where all package with their tasks, components, variables and parameters are defined
3. FileFormats: where external file connections metadata is defined

In this section, we will explain how to add a Biml script to the solution, then we will build each part then we will generate an SSIS package from the whole script.

Adding a new script

To add a new script to the solution, in the menu strip go to “Extensions” > “BimlExpress” > “Add New Biml File” as shown in the image below:

Figure 1 – Add a new script to the solution

A new script will be added within the Miscellaneous folder. Open the script and you will find the main XML element:

FileFormats

This part is used to define the flat-file metadata to be used within the Flat File connection manager. Within this part, we have to define the flat file structure within a FlatFileFormat element where we have to specify different attributes such as Name, CodePage, TextQualifier, RowDelimiter and ColumnNamesInFirstRow. Then for each column, we have to add a Column element as shown below:

Connections

All connection managers must be defined within the connections element. In this example, we need to add two elements within “Connections”:

• Connection for the OLE DB Connection manager where two attributes must be defined; name and ConnectionString
• FlatFileConnection for the Flat File connection manager where three attributes must be defined; name, FilePath and FileFormat (we have to select the FlatFileFormat name attribute defined in the section above)

The connections element will look like the following:

As you noted, we didn’t set the flat file ConnectionString expression in this part since it will be defined within the package part.

Packages

This is the main part where we have to define each package with every included object. First, we have to add a package element that contains the name and other attributes.

Within the package element, we have to define variables, tasks, parameters and connections configuration (expressions) as the following:

Variables

In this element, each variable must be defined using the “Variable” element as the following:

Parameters

In this element, each variable must be defined using “Parameter” element as the following:

Connections

Within this element, we have to add a Connection element where ConnectionName attribute is used to specify the related connection manager, then we can set the expressions within this element by adding an “Expression” element to define the ConnectionString expression (read the flat file path from “@[User::SourceFlatFile] variable) as the following:

Tasks

The “Tasks” element is used to define tasks within the main control flow or containers (For Loop, Foreach Loop, Sequence containers).

First, we have to define the Execute SQL task that create the destination table:

Next, we should define the Foreach Loop container, set the enumerator type to “File enumerator”, and to evaluate the DirectoryPath property as expression (read from SourceDirectory parameter), also we have to map the enumerator output to SourceFlatFile variable.

Finally, we should add another <Tasks></Tasks> element to define all tasks within the container. In this example we should add a DataFlow element where we add a FlatFileSource, DerivedColumns and OledbDestination as following:

• FlatFileSource must be linked to the Flat File connection manager
• DerivedColumns must contain two DerivedColumn; one for the user name and the other for the current date and time
• OleDbDestination must be linked to the OLE DB Connection manager, and the destination must read the table name from the DestinationTable variable

The Biml code should look like the following:

Final Biml Script

The final script should look like the following. We will not cover discussing each attribute and properties in the script element since their names and values are identical to those found within the SSIS editor.

Generating SSIS Package

In order to generate an SSIS package, in the solution explorer tab, Right-Click on the Biml Script file and click on “Generate SSIS packages”:

Figure 2 – Generate SSIS package from a script

A new package will be added to the solution. The following screenshots are for the package control flow, and data flow task where all defined objects are added.

Figure 3 – Control Flow screenshot

Figure 4 – Data Flow task screenshot

Conclusion

In this article, we provided a step-by-step guide to create an SSIS package from a Biml script. We explained how to add some of the most used SSIS tasks such as Execute SQL Task, Foreach Loop Container, and Data Flow Task. In addition, we defined variables and parameters and evaluated some of the tasks and components properties as expressions.

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