Business intelligence development using BIML / BIML Script / BIDS Helper / Mist / BIML Studio

What is BIML?

BIML is a markup language similar to XML that is used to specify Business Intelligence (SSIS and SSAS). BIML will help you to generate SSIS packages and to create SSAS cubes. 

BIML was developed by Varigence an ex Employee of Microsoft (Scott Currie). There is a free tool available on CodePlex named BIDSHelper. Varigence also provides a free Visual studio plugin named BIML Express. Varigence also provides a licensed product called BIML Studio with more features.

Why we use BIML?

To develop consistent SSIS packages and SSAS Cubes we should use BIML.  Second, we should use it to reduce man efforts to create multiple SSIS packages of a similar nature using Metadata driven BIML framework. 

How does it work? We should create a Metadata database that stores metadata related to packages and that reads via BIML with a combination of BIML Script (C#.Net, VB.Net) etc.

BIML is used to create reusable components of SSIS tasks and increase production and maintainability. It is easy to learn because it is like XML tags. 

Metamodel as a source for BIMLScript

We are here to generate multiple packages using BIML and BIMLScript. How does metamodel help to generate multiple packages?

We have to create a metadata database that contains all the required details to generate packages. Like Source/Destination/LookUp Connections, Source/Destination/LookUp Tables, Source/Destination/LookUp columns, and all details that we configure at SSIS Control Flow/Data Flow transformation tasks.

Take the example of Simple Package for a source to destination dump.

1. Connection
2. dbObjects
3. Package
4. Project
5. ColumnDetail
6. ColumnMappings

and many more.

BIML / BIMLScript /Mist Project

Now it's time to get your hands dirty with BIML and BIML Script that read metadata and generate BIML files for each package including the required dependant files (connection, file format, file connection etc)

Before we start you can get the required application installed (BIDSHelper, BIM Express, Mist, BIML Studio). We are going to use BIDS Helper from Codeplex as this is the free version.

Follow steps to create your first BIML project using Metamodel.

1. Open Visual Studio

2. Create new BIML file

3. Write code as below. This is the skeleton of an SSIS Project with a sample package of source and destination.

<#@template language="C#" hostspecific="true"#>
<#@import namespace="System.Data" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<#
String metadataConnectionString = "Data Source=MetaDataServer;Integrated Security=SSPI;Initial Catalog=BIMLMetaDb;Provider=SQLNCLI11.1;Auto Translate=False;";
#>
<#
DataTable fconnections = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT * FROM Connection");
foreach (DataRow rowConnection in fconnections.Rows)
{ #>
<FlatFileConnection Name="<#=rowConnection["ConnectionName"]#>" FileFormat="<#=rowConnection["ConnectionName"]#>" FilePath="<#=rowConnection["ConnectionString"]#>" DelayValidation = "true">
</FlatFileConnection>           
<# } #>
</Connections>
<FileFormats>
<#
DataTable fileConnections = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT * FROM Connection");
foreach (DataRow rowFlatFile in fileConnections.Rows)
{ #>
<FlatFileFormat CodePage="1252" ColumnNamesInFirstDataRow="<#=rowFlatFile["IsFirstRowHeader"]#>" FlatFileType="Delimited" Name="<#=rowFlatFile["ConnectionName"]#>" RowDelimiter="<#=rowFlatFile["RowDelimiter"]#>" IsUnicode="false" TextQualifer="<#=rowFlatFile["textqualifier"]#>">
<Columns>
<#
DataTable FlatFileColumn = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT * FROM Columns WHERE TableName = <TableName>");
foreach (DataRow rowCols in FlatFileColumn.Rows) { #>
<Column ColumnType="Delimited"  Delimiter="<#=rowCols["ColumnDelimiter"]#>" DataType="<#=rowCols["ColumnDataType"]#>" Name="<#=rowCols["ColumnName"]#>"  />
<# } #>
</Columns>
</FlatFileFormat>
<# } #>
</FileFormats>
<Projects>
<PackageProject Name="Sample">
  <Parameters>
<Parameter Name="FilePath" DataType="String">C:\File\</Parameter>
  </Parameters>
  <Packages>
<#DataTable tblPackages = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT [PackageName] FROM dbo.Package "); #>
<# foreach (DataRow row in tblPackages.Rows){ #>
<Package PackageName="<#=row["PackageName"]#>"></Package>
<#}#>
  </Packages>
</PackageProject>
</Projects>
<Packages>
<#
DataTable packagesTable = ExternalDataAccess.GetDataTable(metadataConnectionString, "SELECT  * FROM Table  <Get All package details with requried confguration>");

foreach (DataRow row in packagesTable.Rows)
{ #>
<# int maxcolcount= 0;  #>

<Package Name="<#=row["PackageName"]#>" FailPackageOnFailure="true" FailParentOnFailure="true" ProtectionLevel="EncryptSensitiveWithUserKey" ConstraintMode="Linear">
<Variables>

<Variable Name="RowCount" DataType="Int32">0</Variable>
</Variables>
<Connections>
<Connection ConnectionName="<#=row["DestinationConnectionName"]#>">
  <Expressions>
<Expression PropertyName="<#=row["DestinationConnectionName"]#>.ConnectionString">
@[User::FolderName]+@[User::FileNameExpression]+".dat"
</Expression>
  </Expressions>
</Connection>
</Connections>
<Events>
<Event EventType="OnError" Name="<#=row["PackageName"]#>_OnError"  ConstraintMode="Linear">
<Tasks>
<ExecuteSQL ConnectionName="<#=row["AuditConnectionName"]#>" Name="SQL Package AuditFailure" DelayValidation="true">
<DirectInput>
<![CDATA[
Query for Error Log
]]>
</DirectInput>
<Parameters>
<Parameter DataType="Guid" VariableName="System.ExecutionInstanceGUID" Direction="Input" Name="0" ></Parameter>
</Parameters>
</ExecuteSQL>
</Tasks>
</Event>
</Events>
<Tasks>
</ExecuteSQL>
<#string DFTName = "DFT "+row["PackageName"];#>
<#string OLE_SRCName = "OLE_SRC "+row["FileNameFormat"];#>
<#string FF_DSTName = "FF_DST "+row["FileNameFormat"];#>
 <FileSystem  Operation="CreateDirectory"  Name="FSYS Create Directory"  OverwriteDestination="true">
<VariableInput VariableName="User.FolderName"/>
</FileSystem>
<Dataflow FailPackageOnFailure="true" FailParentOnFailure="true" Name="<#=DFTName#>">
<Transformations>
<#
string str = "";
#>
<#
str = "DFT Query to get Source columns details";
DataTable TableColumns = ExternalDataAccess.GetDataTable(metadataConnectionString, str);
#>
<OleDbSource ConnectionName="<#=row["SourceConnectionName"]#>" Name="<#=OLE_SRCName#>" >
<DirectInput><![CDATA[<#=row["DBObjectName"]#>]]></DirectInput>
<Columns>
<#
int iColCount=0;
foreach (DataRow rowTableCols in TableColumns.Rows)
{
#>
<Column SourceColumn="<#=rowTableCols["SourceColumnName"]#>" SortKeyPosition="<#=iColCount#>" >
</Column>
<# iColCount++;} #>
<# maxcolcount = iColCount; #>
</Columns>
</OleDbSource> 
<RowCount Name="CNT Row Count" VariableName="User.RowCount">
<InputPath OutputPathName="<#=OLE_SRCName#>.Output" />
</RowCount>
<FlatFileDestination ConnectionName="<#=row["DestinationConnectionName"]#>" Name="<#=FF_DSTName#>">
<Columns>
<#
foreach (DataRow rowTableCols in TableColumns.Rows)
{
#>
<Column SourceColumn="<#=rowTableCols["SourceColumnName"]#>" TargetColumn="<#=rowTableCols["TargetColumnName"]#>" >
</Column>
<# } #>
</Columns>
</FlatFileDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>

4. Right click on BIML file and hit "Generate SSIS Package"

5. It will create SSIS packages in the same project.

Hope you enjoyed the new learning experience. Now, it's up to you how you avoid your mundane task of creating and modifying similar nature of SSIS packages.