Martin's Blog

This is the second post in the series about my first Biml project. As I’ve mentioned in the first post, starting small is my recommended approach so that you can find your feet and learn the basics before jumping in completely. Let’s take a closer look at my first attempt…

The project environment

I have an existing Visual Studio solution/project for SSIS 2014, in project deployment mode. Project-level connection managers are defined for my source system (RLM, which is an ODBC source) as well as my destination SQL Server staging database (Staging). Pretty standard stuff so far, and forget about the others in the list for now (this is a real project).

The package

The SSIS package I am trying to recreate with Biml is relatively simple. It contains 3 steps:

1. Execute a stored procedure to get some date parameters from my admin database (BI_Admin). These date parameters will be used to filter the data from my ODBC source table because it is fairly large.
2. Truncate the staging table.
3. Populate the staging table from the ODBC source.

The layout of the package is shown in the image below. As you can see, there is a sequence container that encapsulates all of the other tasks. This is something I like to do as personal preference.

The challenges

As elementary as this package is from a process perspective, we know that there will be a few challenges we’re going to have to deal with.

The first is the fact that we have variables in the package, and we have to set the values from the result set of our stored procedure execution. It shouldn’t be too much of an ordeal, but something to keep in mind as we work through the process.

Using an ODBC data source can cause a few (unexpected) headaches which I will point out in a future blog post, and using an expression to dynamically set the query we execute against this data source could also require some fancy footwork.

All of a sudden this package doesn’t seem as “simple” as it should anymore, and that is why it’s important to start off slowly and take one step at a time.

Step 1: Create the basic package framework

If you’re somewhat familiar with XML, the basic framework to create a package will be super easy. The primary element in each file will be the <Biml> tag and to create a package we simply need a <Packages> element for the collection of packages we want to generate, as well as a <Package> element for the actual package.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

    <Packages>
 
        <Package Name="Staging_PD8001" DelayValidation="true" ProtectionLevel="DontSaveSensitive">
 
        </Package>
 
    </Packages>
 
</Biml>

The code snippet above will generate an empty SSIS package called Staging_PD8001.dtsx, and set the DelayValidation and ProtectionLevel properties. Luckily for us, BimlExpress has auto-complete so there is no reason to memorize all of the possible properties and values.

If you’re not that familiar with XML (or HTML for that matter), it is important to remember that XML is case sensitive and that an open and closing tag (ex. <Package> and </Package>) is required for each element. If there aren’t any child elements (as is the case with our package element right now), we could also have used <Package /> as shorthand notation.

The syntax shown above should also be your first clue as to how you could automate things in future. The mere existence of the <Packages> element indicates that we could possibly generate many packages with this one Biml file.

Step 2: Add connections

My SSIS package needs two OLE DB connections for the SQL Server tables I am going to use (admin & staging databases), and one ODBC connection for the source database (in my case it is an iSeries DB2 database). Adding the following snippet to our Biml file will do the deed:

<Connections>
   <OleDbConnection Name="BI_Admin" CreateInProject="true" ConnectionString="Data Source=<>;Initial Catalog=BI_Admin;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
   <OleDbConnection Name="Staging" CreateInProject="true" ConnectionString="Data Source=<>,1201;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
   <OdbcConnection Name="RLM" CreateInProject="true" ConnectionString="<>" DelayValidation="true" />
</Connections>

Note: I am using the “<>” notation to obfuscate some sensitive information.

Creating connections at the project level is as easy as setting the CreateInProject property to “true”, and you can copy your connection string information from an existing package if you’d like.

Here’s what the complete Biml file looks like after our additions:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
    <Connections>
        <OleDbConnection Name="BI_Admin" CreateInProject="true" ConnectionString="Data Source=<>;Initial Catalog=BI_Admin;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
        <OleDbConnection Name="Staging" CreateInProject="true" ConnectionString="Data Source=<>;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
        <OdbcConnection Name="RLM" CreateInProject="true" ConnectionString="<>" DelayValidation="true" />
    </Connections>
 
    <Packages>
 
        <Package Name="Staging_PD8001" DelayValidation="true" ProtectionLevel="DontSaveSensitive">
 
        </Package>
 
    </Packages>
 
</Biml>

 

Bazinga!! We have successfully generated our first package with Biml. Although it doesn’t physically do anything yet, breaking it down and adding small chunks of functionality at a time makes it seem much less daunting and easier to troubleshoot…especially if you’re just starting to learn.

In the upcoming blog posts we will start adding tasks and steps to make this package do something useful, but before we wrap it up I’d like to point a few things out with regards to what we’ve done here:

• Executing the Biml file (by right-clicking and selecting Generate SSIS Packages) will overwrite existing objects (packages, connections, etc.). You should get a notification before it compiles, but I’ve had some cases where it hasn’t. Be aware of this in case your intent is not to overwrite.
• If your package uses a connection, it has to be in the <Connections> element of your Biml file…even if you have already created it in the Visual Studio (or SSDT) project.
• Unless your connections are using Windows authentication, you’ll have to specify user names and passwords in your Biml code. The DontSaveSensitive protection level property will eventually cause the passwords to be stripped, but you should keep this in mind if you’re planning to distribute the Biml file/code as it may not be secure. In my case with the external ODBC data source, I have no other choice but to specify those details but I am also not distributing or saving the files elsewhere.