Skip to content

Package Configurations and Connection Managers in Biml

Biml (Business Intelligence Markup Language) - Package Configurations and Connection Managers in BimlAt work we wanted to start using Biml to speed up development in our existing projects without making too many changes at once. I decided to start by writing Biml files that generates SSIS packages exactly like the ones we already have so we can implement changes faster, and step one was to figure out how to create Package Configurations and Connection Managers in Biml:

– Create an XML configuration file Package Configuration
– Create the Connection Manager specified in the XML configuration file
– Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
– Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
– Create a Parent package variable

I recommend reading Understanding Integration Services Package Configurations by Rafael Salas (b | t) and Getting Started with BimlScript by Paul S. Waters (t) if you’re not already familiar with the basics of SSIS Package Configurations and Biml.

For the examples in this post I have created a small Integration Services Project in Visual Studio 2013 that uses the package deployment model. It contains one SSIS package called SSISPackageConfigurations.dtsx and a Biml File called BimlPackageConfigurations.biml that will generate another SSIS package called BimlPackageConfigurations.dtsx. I also use a database called Configurations with the [SSIS Configurations] table, a database called BimlSource and a database called BimlDestination:
BimlPackageConfigurations Project

Create an XML configuration file Package Configuration
In all our environments (development / test / production) and on our development computers we have an XML configuration file located at C:\SSIS\Configuration.dtsConfig:

In each environment it contains the Configuration for the Connection Manager to the server and database with the [SSIS Configurations] table where all other configurations are stored. First we need to add the Package Configuration pointing to this XML configuration file.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), enable package configurations, click add, choose Configuration type XML configuration file, click Browse, browse to my XML configuration file, click Next, click Reuse Existing, call it Configuration, click Finish and click Close:
SSIS XML configuration file Package Configuration

In Biml I would add a PackageConfiguration to the Package:

The PackageConfiguration element named Configuration has an ExternalFileInput element with the ExternalFilePath attribute pointing to the location of my XML configuration file.

If you right-click on BimlPackageConfigurations.biml in the Visual Studio Project and click Check Biml for Errors, you will not get any warnings and you can right-click and click Generate SSIS Packages. However, when you open the new BimlPackageConfigurations.dtsx package (or close and re-open the SSISPackageConfigurations.dtsx package) you will get the error “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because we haven’t yet created the actual Connection Manager.

Create the Connection Manager specified in the XML configuration file
Next in SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use the server and database specified in the XML configuration file, click OK, click OK again and rename the newly created Connection Manager to Configurations. The Connection Manager will then get the aqua triangle icon on it that indicates that a Package Configuration is used:
SSIS Connection Manager

In Biml I would add the Connections element below the Packages element:

I like to add the Connections element after the Packages element simply because in BIDS / SSDT-BI the Connection Managers are located below the Control Flow, you can add this above the Packages element if you prefer. The attribute Name in the OleDbConnection element is the name we want to use for the Connection Manager, and the ConnectionString attribute has the same value as specified in the XML configuration file.

Again, if you Check Biml for Errors, you will not get any warnings and you can Generate SSIS Packages. However, when you reopen the BimlPackageConfigurations.dtsx package you will still get the same error: “The connection “Configurations” is not found. This error is thrown by Connections collection when the specific connection element is not found.” This is because Biml only creates the tasks and components necessary in the SSIS package. Since we haven’t yet created any tasks that use the Connection Manager we must force Biml to create the Connection Manager by adding a Connection inside the Packages element as well:

The ConnectionName attribute in the Connection element refers to the OleDbConnection attribute Name. When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will now see the Connection Manager named Configurations with the aqua triangle icon on it that indicates that a Package Configuration is used:
SSIS Connection Manager

Create SQL Server Package Configurations that use the Connection Manager specified in the XML configuration file
Next I want to create the Connection Managers that will be used in the data flow source and destination, and these Connection Managers should get their values from the [SSIS Configurations] table.

In SSIS I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the SQL Server configuration type, choose the Configurations connection, choose the [dbo].[SSIS Configurations] table, choose the BimlSource filter, click Next, click Reuse Existing, call it BimlSource, click Finish, repeat all the steps for BimlDestination and click Close:
SSIS SQL Server Package Configurations

In Biml I would add two new PackageConfigurations:

These PackageConfigurations have ConnectionName attributes that refer to the OleDbConnection named Configurations. They have ExternalTableInput elements with a Table attribute that refers to the [SSIS Configurations] table. The most important thing to notice is that the Name attribute is also used as the filter in the [SSIS Configurations] table:
SSIS and Biml SQL Server Package Configurations

Create Connection Managers specified in the [SSIS Configurations] table in SQL Server
Next we have to repeat the steps of actually creating the Connection Managers.

In SSIS I would right-click in Connection Managers, click New OLE DB Connection, click New, use server .\SQL2012 and database BimlSource, click OK, click OK again and rename the newly created Connection Manager to BimlSource. I would do the same for the BimlDestination Connection Manager.

In Biml I would add two new OleDbConnections and force the creation of the Connection Managers by adding two new Connections to the Package:

When you Generate SSIS Packages and reopen the BimlPackageConfigurations.dtsx package you will see the two new Connection Managers named BimlSource and BimlDestination with aqua triangle icons on them that indicates that Package Configurations are used:
SSIS Connection Managers

Create a Parent package variable
Finally I would like to add a Parent package variable that gets the PackageID from the parent package and stores it in the variable ParentPackageID.

In SSIS I would first create a variable named ParentPackageID:
Parent Package Variable
Then I would open Package Configurations Organizer (SSIS → Package Configurations / right-click in Control Flow → Package Configurations), click add, choose the Parent package variable configuration type, specify the Parent variable PackageID, click Next, browse to the ParentPackageID Variable and choose the Value property, click Next, call it ParentPackageID, click Finish and click Close:
Parent Package Configurations
Parent Package Configuration
The variable named ParentPackageID will now have the aqua triangle icon on it that indicates that a Package Configuration is used:
Parent Package Variable configured in Package Configurations

In Biml this is a lot easier! You only need to add a Variable named ParentPackageID, set the required attribute DataType to Int32, and then add the attribute InheritFromPackageParentConfigurationString with the value PackageID:

When you Generate SSIS Packages and re-open the BimlPackageConfigurations.dtsx package, you will see that you have got a Parent package configuration and the ParentPackageID variable with the aqua triangle, just by adding the attribute InheritFromPackageParentConfigurationString to a Variable.

Conclusion
This post is a basic introduction to Package Configurations and Connection Managers in Biml. It shows how to add an XML configuration file Package Configuration that points to the [SSIS Configurations] table where all other configurations are stored, how to get the other configurations from the [SSIS Configurations] table, and how to create the Connection Managers used in the package. It also shows how to add a Parent package variable. I hope new Biml developers will find it useful!

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments