Seyfeddine Aloui

What is BIML?

BIML is a dialect of XML and can be used to specify business intelligence and data warehouse solutions. It is 100% compatible with SSIS and SSAS. When you generate for example an SSIS package, you can further edit it in Visual Studio, as if you created the package manually.

The language is developed by Varigence, but an open-source implementation is available in BIDS Helper, a free add-on for Visual Studio. This means you can easily develop a BIML script inside BIDS and generate SSIS packages directly into your project.

BIML supports SSIS 2005, 2008(R2) and 2012, although not every feature of the project deployment model is currently implemented in BIDS Helper.

How do I start using BIML?

As mentioned in the previous paragraph, you simply install BIDS Helper on your machine, if you haven't already. To add a new BIML file to your project, simply right-click on the project or the SSIS packages node in the solution explorer. In the context menu, choose Add New BIML File.

A file called BIMLScript.biml will be added to the Miscellaneous folder. This BIML file has an empty BIML root element to start with.

As you can see in the screenshot above, Visual Studio offers color coding for the BIML, as it is just XML. BIDS Helper also offers some basic intellisence features:

When you type "<", you'll get a list of all the possible child elements for the current element. BIDS Helper will insert the corresponding closing tag when you close an element. When typing inside an element, you'll get a list of the attributes you can configure.

The intellisense reports XML violations - such as not closing an element - but also when you forget to configure required attributes.

The BIML can be checked for errors by right-clicking the file and choosing Check BIML for Errors. The compiler doesn't always give the most straight forward error messages, so you might want the check for errors often when developing large BIML files.

When you choose Generate SSIS Packages, BIML will check for errors by validating the XML and BIML syntax, but also by validating the resulting SSIS packages. For example, if a destination table used in a data flow does not already exist, an error will be thrown. Of course, packages are not generated when any error is found during compilation.

A small BIML example

Connections are defined outside any package. Once you refer to a connection in a package, it is added to this package when it is generated by BIML. The next step is to add a package to the BIML script. Small remark: if you are working with SSIS 2005 or 2008, you might want to change the version number of the native client in the connection string. For example, SQLNCLI10.1 for SSIS 2008.

The following snippet will create an empty package called HelloWorld.

========================================================================

========================================================================

The constraint mode defines the default behavior of tasks inside the package. When Parallel is specified, no precedence constraints are defined between tasks. With the Linear constraint mode, each task is connected with a Success precedence constraint to the previous task, following the order of specification in the BIML file.

Let's add the two Execute SQL Tasks to our package. This code will add the first Execute SQL Task, which will check the existence of the destination table and create it if necessary.

========================================================================

   
      
            IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
                       OBJECT_ID(N'[dbo].[HelloWorld]') AND type IN (N'U'))
                   DROP TABLE [dbo].[HelloWorld];
            GO
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
                       OBJECT_ID(N'[dbo].[HelloWorld]') AND type IN (N'U'))
            BEGIN
                   CREATE TABLE [dbo].[HelloWorld](
                          [ID] [int] IDENTITY(1,1) NOT NULL,
                          [Message] [varchar](50) NOT NULL,
                          [Timestamp] [datetime2](3) NOT NULL
                   );
            END
            GO
      
   

========================================================================

The Execute SQL Task has two attributes defined: its name and the connection it will use. The task has a child element DirectInput, specifying the query issued against the database. When specifying a query, watch out for special XML characters, such as double quote or the less than sign. You need to replace these with their escaped counterparts. For example, ampersand & becomes &.

The second Execute SQL Task is created with similar code:

========================================================================

      
     INSERT INTO [dbo].[HelloWorld] VALUES ('Hello World!',SYSDATETIME());
      
   

========================================================================

And that's all the code we need to generate our package. The full script - with comments added - looks like this:

========================================================================

  
  
    
  
  
    
    
    
    
      
        
        
          
            IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
                           OBJECT_ID(N'[dbo].[HelloWorld]') AND type IN (N'U'))
                   DROP TABLE [dbo].[HelloWorld];
            GO
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
                            OBJECT_ID(N'[dbo].[HelloWorld]') AND type IN (N'U'))
            BEGIN
                   CREATE TABLE [dbo].[HelloWorld](
                          [ID] [int] IDENTITY(1,1) NOT NULL,
                          [Message] [varchar](50) NOT NULL,
                          [Timestamp] [datetime2](3) NOT NULL
                   );
            END
            GO
          
        
        
          
            INSERT INTO [dbo].[HelloWorld] VALUES ('Hello World!',SYSDATETIME());
          
        
      
    
  

========================================================================

After generating the package, we can admire the result:

The package can now be edited like any other SSIS package. You can validate the package by running it and checking the result in SQL Server.

Conclusion

This tip gave a basic introduction to the language BIML, which you can use to generate SSIS packages. Next tips will dig deeper into BIML to expose its powerful capabilities and will show you how you can use metadata to generate SSIS packages on the fly.

Try BIML out for yourself. You can use the code used in this tip to create your own simple BIML script. The script uses a database called BIML, so you either can create such a database or change the connection string to work with another database.

Hybrid Architecture

Is a cloud-only product a good idea?

• Reduced Complexity. Power BI will be compelling primarily because of the array of integrated functionality it provides. Provisioning such a comprehensive system on-premises would take most customers weeks or months. As with Office 365 and Windows Azure, Power BI instances will deploy in days, not weeks, and future infrastructure upgrades will be left to Microsoft, freeing in-house resources to worry only about the application layer.
• Pace of Evolution. The BI/Data Analytics space is rapidly changing. Mobility, Big Data and traditional Business Intelligence are quickly merging to create a new kind of insight & analytics landscape.  Cloud-based systems can iterate and improve faster than traditional on-premises alternatives.  Ultimately, adopting a cloud-deployed system will benefit customers by providing more modern, up-to-date systems that keep up with their evolving BI/Analytics needs.
• Broadened Adoption. By removing the need to make large up-front infrastructure investments, Power BI will make the decision to use the technology much easier for more customers--ultimately broadening adoption of advanced BI capabilities.

How will Power BI change the game?

• End Users. End-users have long wanted the ability to easily access and analyze data.  Q&A’s natural language query and intelligent visualization engine will do more to encourage every employee to use information as a basis for decisions. 
• BI Analysts. Power Pivot gave power users the ability to build efficient data models over large data volumes, but that information needed to “fit” into clean data models.  Power Query’s data transformation and flexibility further empower analysts to build their own end-to-end solutions quickly.
• IT & Data Stewards. Self-service BI has always been a push-pull topic.  Everyone wants users to have the tools and information they need to make good decisions.  Yet proper governance, security and auditing is often seen as a road-block to progress by power users.  The data catalog underlying Power BI is exciting because it promotes self-service and governance simultaneously.

• The run-time engine implements the control flow and package management infrastructure
• The data flow engine is a specialized, high performance engine that is exclusively dedicated to extracting, transforming, and loading data.
• Integration Services supports both native and managed code.
• The Integration Services service, monitors running Integration Services packages and manages the storage of packages.