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/)

SQL:Calculating Ideal Default Buffer Max Row and Default Buffer Size in SSIS

Calculating Default Buffer Max Row  and Default Buffer Size

Calculate the Table Size using

Exec sp_spaceused  ‘tablename’

This should give you table size occupied in KB and the no of Records in the table

SQL:PIVOT AND UN-PIVOT - SQL

PIVOT AND UN-PIVOT - SQL

In several scenarios we would have to change the rows to columns and vice versa in a our select query to overcome some bad database designs.Unpivot and pivot does exactly that. All though SSIS provides you with the feature to do unpivot and pivot , it is highly advisable to do it in sql query , unless you have the data coming  from a non – oledb source or from two different sources.

UNPIVOT

Unpivot rotates the columns of the table to the column values.

Following shows a simple example

Term	ENGLISH	MATHS	SCIENCE	FRENCH
MidTerm1	78	86	76	78
MidTerm2	77	44	76	56
MidTerm3	98	47	87	76
MidTerm4	35	89	77	69

SELECT   Term ,

        Subject,

         Marks

FROM     (SELECT Term,ENGLISH,MATHS,SCIENCE,FRENCH

          FROM Scores WHERE STUDENT_ID = 345950) p

         UNPIVOT

         (Marks

          FOR Subject IN ([ENGLISH],[MATHS],[SCIENCE],[FRENCH] ) ) AS unpvt

ORDERBY Term,

        Subject;

GO

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: An Error Handling approach

Error Handling in SSIS

The most common methods to handle the errors in the SSIS package are

1.Redirecting the Error Row along with the Error Code and Error Column in the DFT
2.Make use of event handlers for logging the errors in the Control Flow

By making use of Event Handlers you can Log custom messages in to custom tables during OnWarning , OnError events on the control flow tasks.This could be done by inserting custom error messages into the table using execute sql task or a Script component.

However while ETL i.e in a DFT you could redirect just the error rows and log error message for them.By Redirecting the Error Row you get the data of all the fields in the Error Row along with couple of additional fields ErrorCode and ErrorColumn.The Error Row itself could be used while logging.Its a shame that SSIS does not provide you the information of the exact column where the Error Occurred.The Error Column should not be mistaken for the column name , if you noticed it before its data type is an integer and it provides the lineageid of the respective column where the Error Occurred.

LineageId is something which is generated by the SSIS itself to identify the fields individually.If you open SSIS package as an XML you could view these lineage Ids assigned for each of the fields .

Now I am looking at the other option of getting the Error Description from the Error Code and using that in the custom Log Table which I created.Again the Error Description can be derived from Error Code using the following C# Script in the Script Component

Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode);

The Error Description here again is not meaningful since it is a system generated error message and is of little use to trace down the Error Column.
To get the Error Column there are few Custom Components available online , but due to lack of supporting documents and security reasons I tried not to implement them.

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.