You are likely reading this series to learn how to be a more productive SQL Server Integration Services developer. I applaud your desire and decision, and I sincerely hope the information contained herein has provided ideas and information to help you be more productive. I am always on the lookout for ways to become a better data integration developer. Specifically, I seek out ways to improve code quality and reduce the amount of time required to build solutions. Those goals motivated me to begin practicing patterns-based development in the first place, which eventually led to the idea for the SQL Server 2012 Integration Services Design Patterns book.
This article is adopted from a chapter in the SQL Server 2012 Integration Services Design Patterns book – the chapter entitled Business Intelligence Markup Language. If you are interested in learning patterns of data integration using SSIS, I encourage you to obtain a copy of the book. Although the book focuses on SSIS 2012, most of the material applies to earlier versions of SSIS. In this article we will set up BIDSHelper in SQL Server Data Tools (Visual Studio 2010). We will build a simple Biml file that will, in turn, build a simple SSIS 2012 package.
Business Intelligence Markup Language (Biml) represents SSIS packages using XML. By storing metadata that describes SSIS packages in XML, Biml approaches data integration development from the perspective of a domain-specific language. Business Intelligence Markup Language provides another means to materialize SSIS design patterns – something other than an SSIS package library containing template packages. Regardless of which mechanism used, storing design patterns facilitates code production at a consistent and repeatable quality. That may sound innocuous but I assure it is important; and it is one of the primary reasons to use design patterns in the first place.
Biml is a complex language. You would do well to gain an understanding of domain-specific languages, XML, and .Net development before diving into Biml development proper. I will not delve into the underlying architecture of Biml in this chapter. I will show you some of the mechanisms and direct you to the Biml documentation website: http://www.varigence.com/Documentation/. I believe this is enough to whet your appetite while demonstrating the power of Biml.
A Brief History of Business Intelligence Markup Language
In early 2007, the Microsoft Customer Service and Support (CSS) business incubated a new approach to building business intelligence solutions. As the organization responsible for managing all front-line customer support interactions, CSS has significant analytical and predictive business intelligence needs – across data from a wide variety of sources. To accelerate the development of its internal solutions, CSS began the development of the Vulcan project, which used an XML-based markup language to describe a subset of SQL Server Integration Services packages. This created a model where business intelligence solutions could be developed more rapidly and iteratively by globally distributed teams of BI developers.
After a period of significant success building new BI capabilities, CSS and the SQL Server product team decided to publish the source code for the Vulcan project on CodePlex to enable customers to try the technology and begin building a community around it (http://vulcan.codeplex.com). Feedback from customers recognized that the approach was powerful and promising, but that the implementation reflected the project’s status as an internal tool used to accelerate an operational delivery team. Without documentation and training resources, usability considerations, and additional features, the cost of adopting Vulcan was prohibitive for all but the most determined customers.
In late 2008, Scott Currie, who worked with the Vulcan technology in CSS, founded Varigence, Inc. Varigence created the Business Intelligence Markup Language (Biml), along with tools to enable its design and development. While Biml didn’t directly use any code or technology from Vulcan, the approach taken by the Vulcan project inspired the Varigence team to build Bimlas an Xml-based markup language with rapid, iterative global team development capabilities in mind.
Biml is now available in proprietary products, open source projects, and has been published as an open language specification. Varigence has developed a Biml-compiler that enables a wide variety of automation and multi-targeting capabilities. Additionally, Varigence offers an Integrated Development Environment (IDE) for Biml called Mist. Mist enables rapid and visual design and debugging features for Biml. The open source BIDSHelper project includes Biml functionality, enabling anyone to write and execute Biml code for free.1
In this chapter, we will leverage the free Biml functionality included with BIDSHelper to dynamically generate SSIS packages.
Note: An object containing Business Intelligence Markup Language is a “Biml File”. Biml files are “executed” to generate SSIS Packages.
Setting Up the SSIS Development Environment
Before we get started with Business Intelligence Markup Language, you will need to download and install the latest version of BIDSHelper from http://bidshelper.codeplex.com:
Figure 1 – Download BIDSHelper from Codeplex.com
At the time of this writing, BIDSHelper version 1.6.3.0 is the most current version. Note there are versions of BIDSHelper for Business Intelligence Development Studio (or BIDS, from which BIDSHelper derives its name) for SSIS development in SSIS 2005, 2008, and 2012.
Once downloaded, double-click the installer file to start the installation as shown in Figure 2:
Figure 2 – The BIDSHelper Setup Wizard Welcome Screen
Click the Next button on the BIDSHelper Setup Wizard Welcome screen to display the License Agreement screen:
Figure 3 – The License Agreement screen
Accept the terms of the license (or not, but you do not get to continue until / unless you accept them) and click the Next button on the License Agreement screen to display the Install Location screen:
Figure 4 – Install Location Screen
Click the Install button to install BIDSHelper. Once complete, the Completing the BIDS Helper Setup Wizard screen displays, as shown in Figure 5:
Figure 5 - Completing the BIDS Helper Setup Wizard Screen
Click the Finish button to close the BIDSHelper Setup Wizard.
Building Your First Biml File
Note: This article describes using Biml with SQL Server 2012 Integration Services (SSIS 2012) inside the Visual Studio 2010 (VS 2010) Integration Development Environment (IDE). In SSIS 2012, the VS IDE is named SQL Server Data Tools (SSDT). Confusingly, there is another VS IDE named SQL Server Data Tools that ships with Visual Studio. It is used to design database solutions in Visual Studio.
Once BIDSHelper is installed, open SQL Server Data Tools (SSDT). Create a new SSIS solution and project named “Biml” as shown in Figure 6:
Figure 6 – Creating a New SSIS Project Named “Biml”
Note: If you are new to SSIS, you can learn more about creating SSIS projects and solutions by reading What is SSIS? Level 1 of the Stairway to Integration Services.
Once the Biml solution and project have been created, open Solution Explorer (if it is not already open). Right-click the project name and click “Add New Biml File” as shown in Figure 7:
Figure 7 – Adding a New Biml File
The new file, BimlScript.biml, will be created and assigned to the Miscellaneous virtual folder in Solution Explorer:
Figure 8 – A New Biml File
Double-click the file to open it in the editor. The file begins with the most basic Biml construct, as shown in Listing 1.
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> </Biml>
Listing 1 – Initial Biml Code
Note: You can right-click on the BimlScript.biml file and click “Generate SSIS Packages” as shown in Figure 9:
Figure 9 – Generating an SSIS Package
If you do this, nothing will happen because there is not enough information in the BimlScript.biml file from which to construct an SSIS package. Let’s change that. Add XML so that your Biml file reads as shown in Listing 2:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="TestBimlPackage" ConstraintMode="Parallel">
</Package>
</Packages>
</Biml>
Listing 2 – Biml After Adding Package XML Metadata
Save the BimlScript.biml file, right-click BimlScript.biml in Solution Explorer, and again click “Generate SSIS Packages.” Figure 10 shows a new SSIS package named TestBimlPackage.dtsx is created in the project and file system. The package shows up in Solution Explorer as part of this project:
Figure 10 - TestBimlPackage.dtsx has been created
Let’s return to the BimlScript.biml file and add a task. Create a new XML node beneath the <Package> tag named “Tasks.” Between the <Tasks> and </Tasks> tags, add a new node named “ExecuteSQL”.
Note: If you are not seeing IntelliSense with Biml, follow this link: http://bidshelper.codeplex.com/wikipage?title=Manually%20Configuring%20Biml%20Package%20Generator&referringTitle=xcopy%20deploy for Biml IntelliSense configuration instructions.
Add an attribute to the ExecuteSQL root node named “Name” and set its value to “Test Select”. Create a new XML node between the <ExecuteSQL> and </ExecuteSQL> tags named “DirectInput”. Between the <DirectInput> and </DirectInput> add the T-SQL statement “Select 1 As One”. If you are playing along at home, your BimlScript.biml file should look like Listing 3.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="TestBimlPackage" ConstraintMode="Parallel">
<Tasks>
<ExecuteSQL Name="Test Select">
<DirectInput>Select 1 As One</DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>Listing 3 – Biml After Adding Initial Metadata Describing an Execute SQL Task
After adding the lines of code shown in Listing 3, design-time validation should detect a missing attribute in the ExecuteSQL element: ConnectionName, as shown in Figure 11:
Figure 11 – Design-time Validation in Action
To further test validation, save the BimlScript.biml file and generate the SSIS package from BimlScript.biml in Solution Explorer. Do you get an error similar to that displayed in Figure 12? You should get such an error:
Figure 12 – Missing “ConnectionName” attribute
The Business Intelligence Markup Language engine includes validation functionality and it caught the error in Figure 12. You can invoke a validation from Solution Explorer; simply right-click BimlScript.biml and then click “Check Biml for Errors”.
To fix the error we need to add a Connection Name attribute to the “ExecuteSQL” tag. But we don’t have a connection specified at this time.
To create a connection, return to the top of BimlScript.biml and add the code highlighted in Listing 4. Insert a new line just after the “Biml” tag and before the “Packages” tag. On this line, add the “Connections” XML node. Inside the <Connections> and </Connections> tags, add a “Connection” XML node. A Connection requires two attributes, Name and ConnectionString. I created a connection to the TestDB database on the default instance of the local SQL Server (but you can create a connection to any database since we are not really going to use the connection).
Once the Connection metadata is configured, add a ConnectionName attribute to the “ExecuteSQL” tag. IntelliSense kicks in when you place the cursor after the Name attribute value of the ExecuteSQL tag and before the element close marker – “>” – as shown in Figure 13:
Figure 13 – IntelliSense in Action
The BimlScript.biml file now contains the code listed in Listing 4:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="TestDB" ConnectionString="Data Source=.;Initial Catalog=TestDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package Name="TestBimlPackage" ConstraintMode="Parallel">
<Tasks>
<ExecuteSQL Name="Test Select" ConnectionName="TestDB">
<DirectInput>Select 1 As One</DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>Listing 4 – Biml After Adding Connection Metadata
Test by regenerating the TestBimlPackage.dtsx SSIS package from BimlScript.biml. When we attempt to generate the SSIS package, we see a dialog that confirms we would like to overwrite the existing TestBimlPackage.dtsx SSIS package as shown in Figure 14:
Figure 14 – Confirm Overwrites Dialog
When you confirm this intention, the TestBimlPackage.dtsx SSIS package is regenerated from the metadata contained in the updated BimlScript.biml file. Open the TestBimlPackage.dtsx SSIS package: it should appear as shown in Figure 15:
Figure 15 – A Biml-Generated SSIS package
Alas, there may be another error we must correct – a brand new error in SSIS 2012. You can view it by clicking ViewàError List, as shown in Figure 16:
Figure 16 – ProtectionLevel Mismatch
Since SSIS 2012 Projects can now contain encrypted objects (parameters and connection strings), they now have a ProtectionLevel property. To ensure consistency of encryption across the project and packages, the SSIS 2012 Project ProtectionLevel must match that of every SSIS package in the project. If not, the error in Figure 16 results. To correct this error, right-click the Project Name in Solution Explorer and click Properties as shown in Figure 17:
Figure 17 – Displaying Project Properties
When the Property Pages display, expand the Common Properties group in the left listbox and click Project, and then set the Project ProtectionLevel property in the property grid on the right as shown in Figure 18:
Figure 18 – Setting the Project ProtectionLevel
Close the project property pages. Next, click in the white space of the package and observe the package properties (F4). Specifically, the ProtectionLevel property as shown in Figure 19:
Figure 19 – The Package ProtectionLevel Property
One can change the property here, but it will not save the value in the BimlScript.biml file. To make that change, we need to add the ProtectionLevel attribute to the Biml, highlighted in Listing 5:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="TestDB" ConnectionString="Data Source=.;Initial Catalog=TestDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package Name="TestBimlPackage" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithPassword">
<Tasks>
<ExecuteSQL Name="Test Select" ConnectionName="TestDB">
<DirectInput>Select 1 As One</DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>Listing 5 – Biml After Adding Package ProtectionLevel Metadata
Regenerate the SSIS package from the BimlScript.biml file. Test the package by pressing the F5 key:
Figure 20 – Success!
Conclusion
In this article we set up BIDSHelper in SQL Server Data Tools (Visual Studio 2010). We built a simple Biml file that built an SSIS 2012 package. We added functionality to the Biml file and regenerated the SSIS package. Finally, a test revealed success!
In the next installment, we will use Biml to build an SSIS package that uses the Incremental Load Design Pattern.
1 From an interview with Scott Currie of Varigence, Inc.