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.