SQL Server Integration Services Design Patterns, Second Edition (2014)

Chapter 20. Biml and SSIS Frameworks

Elegant solutions lead to more elegant solutions. This is one feature of elegant solutions, and Business Intelligence Markup Language (Biml) is an elegant solution. Biml facilitates the rapid development of SSIS packages. As impressive as this is, Biml can do more – much more. We will not be able to explore Biml’s complete feature set in this volume, but I would like to examine some additional functionality.

In this chapter, we will build on the example file from the Business Intelligence Markup Language chapter, adding some additional files and functionality.

Using Biml with an SSIS Framework

Appendix A shows the source code for building a file system-based SSIS Framework. Because SSIS 2014 supports backwards-compatibility via the Package Deployment Model, you can use this Framework in all versions of SSIS (to date). The examples in this chapter are designed to interact with the Framework described in Appendix A.

Image Note  To complete the examples that follow you will need to implement the Framework in Appendix A and complete the examples in the chapter titled Business Intelligence Markup Language.

Adding SSIS Package Metadata to the Framework

Open the solution created in the Business Intelligence Markup Language chapter. The solution should be named “Biml2014.sln.” Add a new Biml file and rename it “FrameworkSQLGen.biml.” Add a second new Biml file and rename it “i-FrameworkSQLGen.biml.” I will use the i-FrameworkSQLGen.biml file to demonstrate parameterizing a Biml solution.

Add the following BimlScript to i-FrameworkSQLGen.biml, as shown in Listing 20-1:

Listing 20-1. Manually Adding SSIS Application Metadata to the Framework

string applicationName = "Staging_Application";
string packageFolder = "I:\\Andy\\Projects\\BIML Demo\\";
string filename = @"G:\out\MyFile.sql";
string connectionString = "Data Source=(local);Initial Catalog=SSISIncrementalLoad_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI;";

The BimlScript shown in Listing 20-1 is a code fragment containing variable declarations with initial values. The applicationName variable is a string that contains the name of the SSIS Application I will be adding to the SSIS Framework. The packageFolder string contains the location of the SSIS packages I generated in the Business Intelligence Markup Language chapter example code. The filename string variable contains the full path to the SQL file that will be generated by the FrameworkSQLGen.biml file. The connectionString variable contains connection information to the source database used by this solution and the example in the Business Intelligence Markup Language chapter.

Open the FrameworkSQLGen.biml file and add the code shown in Listing 20-2:

Listing 20-2. Starting the FrameworkSQLGen.biml File

<#@ template language="C#" tier="1" #>
<#@ include file="i-FrameworkSQLGen.biml" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<# var connection = SchemaManager.CreateConnectionNode("SchemaProvider", connectionString); #>
<# var tables = connection.GenerateTableNodes();
    int e = 10;
    StringBuilder sb = new StringBuilder();

The template directive on line 1 idenitifies the .Net language I will use in this file (C#) and the “tier” of this file.

Image Note  Learn more about Biml tiers and the language constructs in Reeves Smith’s excellent article “Stairway to Biml Level 5 - Biml Language Elements” at SQLServerCentral.com (http://www.sqlservercentral.com/articles/BIML/111305/).

The “include file” directive is next and points to the i-FrameworkSQLGen.biml I built first. Including this file means the variables defined in i-FrameworkSQLGen.biml are available in the FrameworkSQLGen.biml file. I next include four namespaces: System.Data, System.IO, System.Text, and Varigence.Hadron.CoreLowerer.SchemaManagement. The first three refer to .Net libraries; the fourth supplies a reference to a Biml assembly.

The C# application logic begins in earnest starting with Line 7 with the declaration and initialization of the variable named “connection.” Connection is set to the SchemaManager object’s ConnectionNode object via the CreateConnectionNode method. The ConnectionNode is aimed at the ConnectionString supplied from the variable “conectionString” contained in the i-FrameworkSQLGen.biml include file.

Another C# variable named “tables” is declared and initialized using the connection variable’s GenerateTableNodes method. The GenerateTableNodes method creates a collection of table objects ready for consumption by BimlScript.

Two additional variables are declared and initialized: an int variable named “e” which will be used to enumerate the execution order of packages in the Framework, and a StringBuilder variable named “sb” which will be used to build the Transact-SQL command to load the Framework metadata for the SSIS Application.

The Transact-SQL script for adding an SSIS Application to the Framework is listed in Figure A-7 in Appendix A and is shown in Listing 20-3.

Listing 20-3. Manually Adding SSIS Application Metadata to the Framework

Use SSISConfig

declare @ApplicationName varchar(255) = 'SSISApp1'
declare @ApplicationID int

/* Add the SSIS First Application */
If Not Exists(Select ApplicationName
              From cfg.Applications
              Where ApplicationName = @ApplicationName)
  print 'Adding ' + @ApplicationName
  exec cfg.AddSSISApplication @ApplicationName, @ApplicationID output
  print @ApplicationName + ' added.'
  Select @ApplicationID = ApplicationID
  From cfg.Applications
  Where ApplicationName = @ApplicationName
  print @ApplicationName + ' already exists in the Framework.'
print ''

Place this code into the FrameworkSQLGen.biml after the code in Listing 2. I need to load this Transact-SQL into the stringbuilder variable (sb) and I need to make it generic by leveraging the variables in the include file. Accomplish this by loading the code into the stringbuilder as shown in Listing 20-4:

Listing 20-4. Adding SSIS Application Metadata to the Stringbuilder

sb.AppendLine("Use SSISConfig");
sb.AppendLine("declare @ApplicationName varchar(255) = '" + applicationName + "'");
sb.AppendLine("declare @ApplicationID int");
sb.AppendLine("/* Add the SSIS First Application */");
sb.AppendLine("If Not Exists(Select ApplicationName");
sb.AppendLine("              From cfg.Applications");
sb.AppendLine("              Where ApplicationName = @ApplicationName) ");
sb.AppendLine(" begin");
sb.AppendLine("  print 'Adding ' + @ApplicationName");
sb.AppendLine("  exec cfg.AddSSISApplication @ApplicationName, @ApplicationID output");
sb.AppendLine("  print @ApplicationName + ' added.'");
sb.AppendLine(" end");
sb.AppendLine(" begin");
sb.AppendLine("  Select @ApplicationID = ApplicationID");
sb.AppendLine("  From cfg.Applications");
sb.AppendLine("  Where ApplicationName = @ApplicationName");
sb.AppendLine("  print @ApplicationName + ' already exists in the Framework.'");
sb.AppendLine(" end");
sb.AppendLine("print ''");

The Transact-SQL for building parameters to manage package metadata is found in the first sections of Listings A-5 and A-9 and is shown here in Listing 20-5:

Listing 20-5. SSIS Framework Package Parameters

declare @ExecutionOrder int = 10
declare @ApplicationID int = 1
declare @PackageID int = 1
declare @ApplicationName varchar(255) = 'SSISApp1'
declare @PackageFolder varchar(255) = 'F:\Andy\Projects\PublicFramework_PackageDeployment_2014\SSISConfig2014\'
declare @PackageName varchar(255) = 'Child1.dtsx'

Add to the stringbuilder Transact-SQL to declare the parameters that will be used to insert SSIS package metadata into the Framework, omitting some and adding some, as shown in Listing 20-6:

Listing 20-6. Adding SSIS Package Parameters to the Stringbuilder

sb.AppendLine("declare @ExecutionOrder int");
sb.AppendLine("declare @PackageID int");
sb.AppendLine("declare @PackageFolder varchar(255) = '" + packageFolder + "'");
sb.AppendLine("declare @PackageName varchar(255)");
sb.AppendLine("/* Add Packages */");

The real work of this BimlScript is done inside a loop, shown in Listing 20-7:

Listing 20-7. Starting the Loop to Load SSIS Framework Package Metadata

foreach (var table in tables)
        string tbl = "IncrementalLoad_" + table.Name + ".dtsx";

The foreach loop definition creates a new variable named table that will represent each Biml table in the tables collection populated earlier. The tbl string variable is created for use identifying the SSIS package by name.

The remainder of Listings A-5 and A-9 in Appendix A are consolidated and shown in Listing 20-8:

Listing 20-8. Adding SSIS Framework Package Metadata to the Stringbuilder

sb.AppendLine("/* " + tbl +  " */");
sb.AppendLine("Set @PackageName = '" + tbl + "'");
sb.AppendLine("Set @ExecutionOrder = " + e.ToString());
sb.AppendLine("If Not Exists(Select PackageFolder + PackageName");
sb.AppendLine("              From cfg.Packages");
sb.AppendLine("              Where PackageFolder = @PackageFolder");
sb.AppendLine("                And PackageName = @PackageName)");
sb.AppendLine(" begin");
sb.AppendLine("  print 'Adding ' + @PackageFolder + @PackageName");
sb.AppendLine("  exec cfg.AddSSISPackage @PackageName, @PackageFolder, @PackageID output");
sb.AppendLine(" end");
sb.AppendLine(" begin");
sb.AppendLine("  Select @PackageID = PackageID");
sb.AppendLine("  From cfg.Packages");
sb.AppendLine("  Where PackageFolder = @PackageFolder");
sb.AppendLine("    And PackageName = @PackageName");
sb.AppendLine("  print @PackageFolder + @PackageName + ' already exists in the Framework.'");
sb.AppendLine(" end");
sb.AppendLine("        If Not Exists(Select AppPackageID");
sb.AppendLine("              From cfg.AppPackages");
sb.AppendLine("              Where ApplicationID = @ApplicationID");
sb.AppendLine("                And PackageID = @PackageID");
sb.AppendLine("                And ExecutionOrder = @ExecutionOrder)");
sb.AppendLine(" begin");
sb.AppendLine("  print 'Adding ' + @ApplicationName + '.' + @PackageName + ' to Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)");
sb.AppendLine("  exec cfg.AddSSISAppPackage @ApplicationID, @PackageID, @ExecutionOrder");
sb.AppendLine("  print @PackageName + ' added and wired to ' + @ApplicationName");
sb.AppendLine(" end");
sb.AppendLine(" print @ApplicationName + '.' + @PackageName + ' already exists in the Framework with ExecutionOrder ' + convert(varchar, @ExecutionOrder)");
e += 10;

This code inside the loop executes for every package contained in the tables collection populated from the call against the connection object’s GenerateTableNodes() method. Each iteration begins with a comment to identify the name of the SSIS package followed by the setting of the PackageName and ExecutionOrder parameter values. PackageName is set to the name of the SSIS Package contained in the string variable tbl; ExecutionOrder is set to the string version of the integer value contained in the e variable.

Next, a conditional statement which checks for the existence of a package in the Framework is added to the StringBuilder variable, “sb.” If the package metadata is not present, it is added by executing the cfg.AddSSISPackage stored procedure which returns the PackageID parameter. If the package metadata is present, a statement populating the PackageID parameter is executed.

The next portion of the Transact-SQL added to the stringbuilder is a similar section which associates SSIS Package and Application metadata. This is accomplished by adding rows to the cfg.AppPackages “bridge” table – or informing the user that this relationship already exists.

The final statement of the loop increments the value of the variable e by 10.

The last section of BimlScript writes the contents of the stringbuilder to the file specified in the i-FrameworkSQLGen.biml file with the filename variable, as shown in Listing 20-9:

Listing 20-9. Writing the Transact-SQL in the Stringbuilder to the Output File

using (StreamWriter outfile = new StreamWriter(filename))

Executing the Biml File

Let’s test by generating the Transact-SQL file, as shown in Figure 20-1:


Figure 20-1. Execute the FrameworkSQLGen.biml File

Right-click the FrameworkSQLGen.biml file and click Generate SSIS Packages. The file with the filename MyFile.sql should be generated in the location contained in the i-FrameworkSQLGen.biml include file. Open that file in SQL Server Management Studio (SSMS) and execute the SQL statements. Your output should appear similar to that shown in Figure 20-2:


Figure 20-2. Execute the SQL File Generated by the FrameworkSQLGen.biml File

Generating the SSIS Command-Line

We can do even more using BimlScript, though. We can generate the command-line to execute the SSIS Application using the Framework’s Parent.dtsx package.

Open the i-FrameworkSQLGen.biml file and add a couple more variables, as shown in Listing 20-10:

Listing 20-10. Adding Variables to the i-FrameworkSQLGen.biml File

string FrameworkParentPkgPath = @"E:\Projects\SSISConfig2014\SSISConfig2014\Parent.dtsx";
string CmdFilename = @"G:\out\MyCmd.cmd";

Your file locations will be different from those shown in Listing 20-10.

Add a new Biml file to your solution and rename it “GenerateExecCmd.biml.” Add the following statements to GenerateExecCmd.biml, shown in Listing 20-11:

Listing 20-11. Building the GenerateExecCmd.biml File

<#@ template language="C#" tier="2" #>
<#@ include file="i-FrameworkSQLGen.biml" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.IO" #>
<#  StringBuilder sb = new StringBuilder();
    sb.AppendLine("dtexec /FILE " + FrameworkParentPkgPath + " /SET \\Package.Variables[ApplicationName].Properties[Value];" + applicationName);
    using (StreamWriter outfile = new StreamWriter(CmdFilename))

Right-click the GenerateExecCmd.biml file in Solution Explorer and click “Generate SSIS Packages.” Open Windows Explorer and browse to the folder you supplied for the CmdFilename variable in the i_FrameworkSQLGen.biml file. Open the file that was just generated and you should observe a string similar to the one shown in Listing 20-12:

Listing 20-12. The Command Line Generated by the GenerateExecCmd.biml File

dtexec /FILE E:\Projects\SSISConfig2014\SSISConfig2014\Parent.dtsx /SET \Package.Variables[ApplicationName].Properties[Value];Staging_Application

Because this command line is contained in a “cmd” file, you should be able to double-click it watch it execute in a command prompt window, similar to that pictured in Figure 20-3:


Figure 20-3. Executing the Command Line Generated by GenerateExecCmd.biml

How cool is that?


In this chapter, we have extended our knowledge regarding the functionality of Business Intelligence Markup Language. We have demonstrated how to use Biml to dynamically generate Transact-SQL statements and execution command lines.