Creating a Meta Data Driven SSIS Solution with Biml

Biml

Biml (Business Intelligence Markup Language) is a markup language that enables you to quickly represent a variety of database related models and constructs, including SSIS packages, models, permissions and more.

In simple words, Biml can be used to create metadata driven solutions.

Biml is a way of describing your BI  solution in a simple xml format.

BimlScript extends the language by enabling authors to embed c# or vb.Net code with Biml, similar to how ASP.Net includes .NET code with static HTML markup.
Biml authored BI assets can currently be used with the BIDS Helper.(http://bidshelper.codeplex.com/)

SSIS:Best Practices for Better Performance

This article intends to cover the performance improvement techniques and performance constraint scenarios based on the developer’s scope only.

Choice of Transformations

In a real world we would have to do several transformations before the data is actually loaded. The transformations use the buffer memory which in turn affects the performance.So it is very important to understand which transformations influence the performance and how

The transformations can be categorized in to Fully Blocking Transformations, Semi-Blocking Transformations and Non – Blocking transformations.

Fully Blocking Transformations: Blocks the entire dataset to perform the transformation.

Semi-Blocking: Blocks group of data to perform the transformations.

Non – Blocking: No blocking of datasets.

As a general rule, we should try to reduce the number of blocking and semi-blocking transformations.

SSIS:Automated Unit Testing

There are quite a few ways to perform unit testing for a SSIS package and also automate it.One such method is using the BizUnit Framework which is predominantly used for the Biz Unit testing.We can customize BizUnit to test SSIS Package as well.
We should write extension methods in BizUnit Framework to execute the SSIS package, query the database and compare the result with the expected result or even compare the output file with the expected output file.It might be a little extra effort for a SQL BI developer to do some c# coding but its an one time activity and definitely worth it.


Before going ahead let me create a simple package which would raise warnings in to system log table.

SQL:SQL Server Agent - Alerts

SQL Server Agent - Agent Alerts

What is an Alert?

A SQL server Agent Alert is an action in response to an event or a happening in the SQL Server System.
An Alert could be triggered for one of the following conditions

• When an error occurs in the SQL Server System.
• When there is a performance issue or threshold reached in the SQL Server System
• When there is a WMI event

In response to an alert you can either execute a SQL Server Job or email the DBA (Operator).
For automated fix to a problem we could run a Pre defined fix (SQL Server Job) and for human intervention to an issue we could email the information to the Operator.

SSIS:Data Validation

VALIDATION IN SSIS

Why we need Validation ?
I have not thought of validating the input in SSIS package until recently I found myself in a position to do it. In my previous packages I found a way to redirect the error outputs while doing a data conversion. But what about situations where you need to validate the input file row by row before updating in to the destination. The advantage of validating input file is mainly to have custom error messages logged in to my logging table.

For example when the format of my expected input column “A” is not valid then I could log the error message “The Column A is not valid in the Row 120” .This could save a lot of my time to correct the input file or for further analysis.

Pros and Cons