Varigence, Inc.

Varigence is excited to announce that we will be a Gold Sponsor of SQLBits X.  SQLBits X consists of 3 days of amazing SQL Server content and serves as the official UK Technical Launch for SQL Server 2012.  SQLBits will be taking place at Novotel West in London from March 29^th until March 31^st.

Varigence will be exhibiting on site at SQLBits, so stop by our booth and say hello. We’ll be giving away mini-footballs and having a raffle for an IPad 2.  In addition, our CEO Scott Currie will be giving two lunch time presentations.  On Friday, Scott will discuss how to accelerate your BI projects with Varigence tools and frameworks.  On Saturday, he’ll show how you can use a free solution with Business Intelligence Markup Language (BIML) and BIDS Helper to achieve unparalleled performance and usability from SSIS.

Register for SQL Bits now at  

This is the first blog entry focused on helping users prepare for the $10,000 Biml Challenge. This will get you a basic understanding of the BI markup language, Biml.

Biml is an XML-based language for specifying the full set of assets in a BI and/or DW project. With Biml you can design everything from relational databases, to data integration and transformation packages, and OLAP cubes. The Biml language is fully specified online. The rest of the blog entry will give a more informal look into how to use Biml.

Biml works in a way consistent with other XML-based languages. If you want to define something in Biml, specify the element for that item. For example,

When you have a technology that drastically increases the productivity of BI and Data Warehouse developers and architects, it can be difficult to convey just how groundbreaking it is. We believe Mist & Biml are such products.  Once you have the chance to use them, you'll never want to go back to the old way of building your data solutions.

This year at PASS Summit (October 2011, Seattle, WA), we're putting our money where our mouth is by sponsoring the $10,000 Biml Challenge during the exhibition.  

The contestant who solves a typical BI/DW task assigned in the competition the fastest will win $10,000 cash.  This task would normally require hours or longer with traditional approaches, but with Biml and Mist, the time limit will be a generous 15 minutes.

This post is part 5 of a series on using Biml in BIDS Helper. This post builds on some of the information and the sample from the previous posts.

In the previous post in the series, I talked about controlling the order of execution in the control flow. In this post, the focus will be on the dataflow, and controlling how the data in the pipeline flows from one component to the next. This post uses a new table as the target of the data flow, so you may want to review Part 2: Creating Tables using Biml and BimlScript to see how to create the table locally. The Biml below describes the table. You can create it in the database of your choice – I used a database named Target.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;"/>
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;"/>
    </Connections>
    <Tables>
        <Table Name="DimAccount_Test" ConnectionName="Target">
            <Columns>
                <Column Name="AccountKey" />
                <Column Name="ParentAccountKey" IsNullable="true" />
                <Column Name="AccountCodeAlternateKey" IsNullable="true" />
                <Column Name="ParentAccountCodeAlternateKey" IsNullable="true" />
                <Column Name="AccountDescription" DataType="String" Length="50" IsNullable="true" />
                <Column Name="AccountType" DataType="String" Length="50" IsNullable="true" />
                <Column Name="Operator" DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMembers" DataType="String" Length="300" IsNullable="true" />
                <Column Name="ValueType" DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMemberOptions" DataType="String" Length="200" IsNullable="true" />
            </Columns>
        </Table>
    </Tables>
</Biml>

With the table created, we can move on to the interesting part – transforming the data. In a simple, straightforward data flow, the Biml compiler will do most of the work for you. Take this data flow as an example:

This post is part 4 of a series on using Biml in BIDS Helper. This post builds on some of the information and the sample from the previous posts.

So far, we’ve looked at some relatively simple packages, in terms of their flow. In this post, we’re going to look at how to handle more complex control flow in Biml.

One feature of Biml is the ConstraintMode property that’s part of packages and containers. This property controls how precedence constraints are generated in the control flow. In the simple case, if you want all tasks to be run in sequence, you can set the ConstraintMode to Linear. This causes the package to be produced with all tasks connected sequentially by Success precedence constraints, in the order they were specified in the Biml. So, the following Biml:

 This post is part 3 of a series on using Biml in BIDS Helper. This post builds on some of the information and the sample from the previous posts.

BimlScript enables some interesting scenarios for generating large numbers of SSIS packages automatically. This can come in handy when you need to copy data most or all of the data in one database to a different one. In this case, you could use something like theTransfer SQL Server Objects task, but it has a few problems. You can roll your own, but that might mean a fair amount of custom scripting. Or you could use the Import / Export Wizard. But in all these cases, you don’t have complete control of how the packages are produced. You could create all the packages by hand, which does give you full control, but then you are stuck doing a lot of repetitive work in SSIS.

BimlScript provides an alternative that lets you fully control the output, while automating the rote work of producing lots of packages that use the same pattern. Let’s take a look at a sample of this, using the scenario above (copying the data from one database to another).

This article is going to walk through the process of creating a simple package using Biml and the Biml Package Generator feature in BIDS Helper. To start out, you need to install the latest beta of BIDS Helper from CodePlex. Once that is set up, you should create a new Integration Services project in BIDS. In the project, right-click on the Project in the Solution Explorer. There’s a new item in this menu – Add New Biml File.

Clicking Add New Biml File will add a new file to the Miscellaneous folder in the solution named BimlScript.biml. (The name is automatically generated, so it may be BimlScript1.biml, etc). You can right-click on the file and choose rename to give the file a more specific name. For this example, rename the file “BasicPackage.biml”.

For the past three weeks, I’ve been demonstrating how to construct and use SSIS workflow related tasks. Continuing with that theme, I’m going to walk through the Execute Process task.

Background:

The Execute Process task provides flexibility in your package workflow, by allowing you to run a Windows application or batch script. There are tons of scenarios where this may be useful. Common ones include compressing or expanding a file before using it further in your workflow; for instance, you may have a file generated by the WMI Data Reader task that you want to compress before sending it to someone via the SendMail task. Alternatively, if you need custom information that can’t be obtained via an existing SSIS task, you could use Execute Process to run an application that generates the data you need, and then use that data in your workflow.

You've made it to the Varigence blog, welcome! 

The first thing you should know is who we are.  Varigence team members live and breathe business intelligence and application development.  We were brought together by a vision that business intelligence could be made much more efficient and effective.    As a business we currently have one shipping product, Vivid, and another product, Mist, slated for release at the end of January 2011. Varigence also provides consulting and training services to help customers accelerate adoption of our products and frameworks.

The goals of this blog are: