zaterdag 13 oktober 2012

SSIS : My first BIML package (part I)

Introduction

In this blogpost I'll show how to create a simple package using BIML and the BIML Package Generator Feature in BIDS helper. I assume that you already installed BIDSHelper form Codeplex. The next thing to do is creating a new project in in BIDS and start using the BIML Package Generator Feature from BIDSHelper. I've borrowed the code from the tutorials of BIDSHelper.

The BIML script that is used for this blogpost is from the BIDSHelper 1.6.1

Creating a simple BIML package

Click "Add New BIML File" and a new file is added to the miscellaneous folder in the solution called BimlScript.biml. This name is automatically generated and you can change this to another name. Right click on the filename and rename file to MyFirstBIMLPackage.

Double clicking on the file will open the BIML editor. The first lines are already written for you.

The first thing we have to do is creating a connection to a database. This is done in the first section of the package. The next section defines a single package that contains a dataflowtask and the dataflow task contains two components: OLEDBSource and a Multicast.

The next step is to generate a package with this definition. Right click on the BIML file and Generate SSIS Packages from the contextmenu. A new SSIS package appears in the Packages list.

If you view the generated package then you'll see the following package components.

Conclusion

BIML is a very intersting feature of BIDSHelper. I've tried in earlier blogsposts to create a SSIS package with the SSIS API but that is a very timeconsuming and erroneous process. I think that with the BIML support in BIDSHelper you can generate SSIS package a lot quicker.