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  

Microsoft has recently started a new program called SSAS Maestros.  An extremely deep technical training course on Analysis Services, which includes labs and even a take home exam project.  

There were hundreds of applicants and eighty-one attendees.  From that pool Microsoft recently announced their first class of 11 SSAS Maestros, and I'm happy to congratulate my colleague John Welch for being part of this elite group!

I work with John, so I can honestly say I'm not surprised to hear about this, but nevertheless it's a great honor for him.  Way to go John, and congrats to the other ten Maestros!

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.

It’s been a little while since my previous post as we’ve been heads down on our next Mist and Hadron releases. However, I’m back today to begin discussing the several Data Preparation SSIS tasks. I’ve already authored a post on the SSIS File System task and I’m continuing today with a discussion of the FTP task.

Background:

The SSIS FTP task allows you to perform various FTP operations in your package. The task implements eight different operations, although three are duplicated by the File System task:

My previous post returned to the theme of workflow tasks by discussing the SSIS Execute Package task, along with what Mist brings to the table.  It’s now time to finish off the workflow tasks with the Message Queue task.

Background:

Microsoft Message Queuing (MSMQ) is a message queue implementation that’s existed in Windows operating systems for over 15 years. Its intent is to allow applications running on separate servers to communicate in a failsafe manner. From Wikipedia: “MSMQ is responsible for reliably delivering messages between applications inside and outside the enterprise. MSMQ ensures reliable delivery by placing messages that fail to reach their intended destination in a queue and then resending them once the destination is reachable.”

In my last post, I demonstrated the SSIS Foreach File loop container task in conjunction with the File System task. This week, however, I want to return to the theme of workflow tasks and discuss the Execute Package task.

Background:

The Execute Package task has a straightforward purpose; allow a package to run other packages in its workflow. This enables packages to be reusable, modular components that can be shared among workflows. The package being run by the Execute Package task is usually referred to as the child package; the package running the Execute Package task is the parent.

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: