SQL Server Integration Services (SSIS) 10 Quick Best Practices

Here are the 10 SSIS best practices that would be good to follow during any SSIS package development

§ The most desired feature in SSIS packages development is re-usability. In other ways, we can call them as standard packages that can be re-used during different ETL component development. In SSIS, this can be easily achieved using template features. SSIS template packages are the re-usable packages that one can use in any SSIS project at any number of times.

To know more about how to configure this, please see http://support.microsoft.com/kb/908018

§ Avoid using dot (.) naming convention for your package names. Dot (.) naming convention sometime confuses with the SQL Server object naming convention and hence should be avoided. Good approach would be to use underscore (_) instead of using dot. Also make sure that package names should not exceed 100 characters. During package deployment in SQLServer type mode, it is noticed that any character over 100 are automatically removed from package name. This might result your SSIS package failure during runtime, especially when you are using ‘Execute Package Tasks’ in your package.

§ The flow of data from upstream to downstream in a package is a memory intensive task, at most of the steps and component level we have to carefully check and make sure that any unnecessary columns are not passed to downstream. This helps in avoiding extra execution time overhead of package and in turn improves overall performance of package execution.

§ While configuring any OLEDB connection manager as a source, avoid using ‘Table or view’ as data access mode, this is similar to ‘SELECT * FROM <TABLE_NAME>, and as most of us know, SELECT * is our enemy, it takes all the columns in account including those which are not even required. Always try to use ‘SQL command’ data access mode and only include required column names in your SELECT T-SQL statement. In this way you can block passing unnecessary columns to downstream.

§ In your Data Flow Tasks, use Flat File connection manager very carefully, creating Flat File connection manager with default setting will use data type string [DT_STR] as a default for all the column values. This always might not be a right option because you might have some numeric, integer or Boolean columns in your source, passing them as a string to downstream would take unnecessary memory space and may cause some error at the later stages of package execution.

§ Sorting of data is a time consuming operation, in SSIS you can sort data coming from upstream using ‘Sort’ transformation, however this is a memory intensive task and sometime result in degrade in overall package execution performance. As a best practice, at most of the places where we know that data is coming from SQL Server database tables, it’s better to perform the sorting operation at the database level where sorting can be performed within the query. This is in fact good because SQL Server database sorting is much refined and happens at SQL Server level. This in turn sometime results overall performance improvement in package

execution.

§ During SSIS packages development, most of the time one has to share his package with other team members or one has to deploy same package to any other dev, UAT or production systems. One thing that a developer has to make sure is to use correct package protection level. If someone goes with the default package protection level ‘EncryptSenstiveWithUserKey’then same package might not execute as expected in other environments because package was encrypted with user’s personal key. To make package execution smooth across environment, one has to first understand the package protection level property behaviour,

please see http://msdn2.microsoft.com/enus/library/microsoft.sqlserver.dts.runtime.dtsprote

ctionlevel.aspx .

In general, to avoid most of the package deployment error from one system to another system, set package protection level to ‘DontSaveSenstive’.

§ It’s a best practice to take use of Sequence containers in SSIS packages to group different components at ‘Control Flow’ level. This offers a rich set of facilities

o Provides a scope for variables that a group of related tasks and containers can use

o Provides facility to manage properties of multiple tasks by setting property at  Sequence container level

o Provide facility to set transaction isolation level at Sequence container level.

For more information on Sequence containers, please see http://msdn2.microsoft.com/en-us/library/ms139855.aspx .

§ If you are designing an ETL solution for a small, medium or large enterprise business need, it’s always good to have a feature of restarting failed packages from the point of failure. SSIS have an out of the box feature called ‘Checkpoint’ to support restart of failed packages from the point of failure. However, you have to configure the checkpoint feature at the package level.

For more information, please see

http://msdn2.microsoft.com/en-us/library/ms140226.aspx

.

§ Execute SQL Task is our best friend in SSIS; we can use this to run a single or multiple SQL statement at a time. The beauty of this component is that it can return results in different ways e.g. single row, full result set and XML. You can create different type of connection using this component like OLEDB, ODBC, ADO, ADO.NET and SQL Mobile type etc. I prefer to use this component most of the time with my FOR Each Loop container to define iteration loop on the basis of result returned by Execute SQL Task. For more information, please see

 http://msdn2.microsoft.com/en-us/library/ms141003.aspx

&

http://www.sqlis.com/58.aspx

Conditional split vs Multicast Transformation:

Conditional split Transformation:

It is used to split the data based on the conditions. it contains conditional split default output.

Suppose if we have ‘n’ conditions we will get ‘n+1’ outputs.

We can send single record into single destination only that means the records which are satisfied in first condition those records will not come into the second condition.

Multicast Transformation:

It is used to send the data into multiple destinations and we can’t apply any conditions.

We can send single record into multiple destinations.

If we have ‘n’ inputs we will get  ‘n’ outputs.

Displaying Colors in Data Bars based on conditions

By this time we have seen how Data Bars works. Now let us customize it a little bit for displaying custom colors based on conditions. What we will do is that, if the runs made is greater than or equal to 100 we will display Green Color bars else Red color Bars.
Let us enter the below custom code to our report (Kindly refer to Working with expression section for understanding how to enter custom code to report)

'Function to set the color 
Public Shared Function SetColor(ByVal RunsMade As Integer) As String
SetColor= "Red"
If RunsMade  > 100 Then
SetColor= "Green"
End IF
End Function

The code is simple enough to understand. It will return the color as Red or Green depending on the condition.
Now consider we have designed our report as under (we have already seen this earlier. Henceforth, I am not explaining it again)

Let us select the Data Bar for getting the Chart Data popup. Then right-click on the bar and select Series Properties.

In the Series Properties window, select the Fill tab and click the Expression button (fx).

And in the expression editor enter the below expression

 =Code.SetColor(Fields!RunsMade.Value) 

That's it. Now if we run the report, we will get the desired result

Kindly note that, we have given some gradient effect to our report for beautification.

Displaying Data Labels along with Data Bars

Let us see how we can display the data labels along with the data bars.
Right click the Data Bar and choose Show Data Labels.

The Data Label appears as shown below

Right click on the data label and choose the Series Label Properties

In the Series Label Properties window, go to the Border tab and set the Line Style to Solid.

Run the report. As can be figured out that, we now have the Data Bar and the Data Label together.

Working with Data Bars

Data bars are use to make "Tornado charts" in order to get an idea as how values are distributed.
We will use the same data source tbl_Players for this demonstration too.
Objective
In this experiment, we will see how the players runs are distributed using Data Bar.
DataBar into action
In the design view, add a table with three columns. In the first column, let us add [Player Name] field while in the last two column let us add the [Runs Made] column. Let us rename the header of the third column to [Runs Analysis]

From the Report Item toolbox, let us drag and drop a Data Bar control on to the third column. The Data Bar Type properties window appears. From there let us choose the "Stacked bar" Data Bar type

And click OK. Right click on the Data Bar and from the context menu, choose Chart Properties

In the General section of the Chart Properties window, choose Gray Scale from the Color Pallet drop down.

And in the Fill section, choose Gradient in the Fill Style and provide some suitable gradients

Click OK and view the report

We can reverse the direction of the Data Bars. Right click on the Data Bar and from the context menu,choose Horizontal Axis Properties.From the properties dialog check the Reverse Direction checkbox of the Scale options.

The report is as under

Custom Paging in SSRS report

This section will give us the way of generating custom paging in our report. We will also learn the use of Global variable in doing so.
Step 1: In the report design screen, right click and from the context menu, choose Add Page Footer

Once the Page Footer is added, we can then add controls to it.

Step 2: Let us add four textboxes from the Report Item toolbox onto the designer as shown under

Step 3:Choose the 2nd textbox and click on the Expression

Step 4:From the expression window that opens, let us write the expression

=Globals!PageNumber

Similarly for the 4th Textbox, let us write the expression as =Globals!TotalPages.
Step 5:Run the report and the output is as under

Working with Calculated fields

A Calculated field is a field that is derived from another field.
Objective
Suppose we want to twice the match fee for every player who ever has bagged more than 10 wickets and made a score of more than 500 runs. In such a case, that row will be will be green colored and the Calculated column value will be made bold.
Solution
Step 1: As a first step let us write the custom codes
Function: SetColor
Purpose: This function will set the color for the entire row to green if the runs made are more than or equals to 500 and wickets taken are more than or equals to 10.

'Function to set the color
Public Shared Function SetColor(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetColor= "Transparent"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetColor= "Green"
End IF
End Function

Function: SetBoldFontWeight
Purpose: This function will set the Row data to Bold for the Calculated column where the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

' Function to set the font weight as bold
Public Shared Function SetBoldFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String
SetBoldFontWeight= "Default"
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
SetBoldFontWeight= "Bold"
End IF
End Function

Function: DoubleMatchFee
Purpose: Doubles the player match fees if the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

'Function to double the match fee
Public Shared Function DoubleMatchFee(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer,ByVal OriginalMatchFee As Integer) As Integer
DoubleMatchFee= OriginalMatchFee 
If RunsMade   >= 500  AND  WicketsTaken >= 10 Then
DoubleMatchFee= OriginalMatchFee * 2
End IF
End Function

Step 2: Choose Data Set. Right Click and choose Add Calculated field

The DataSet Properties window opens up. Enter a Calculated field Name and click on the Expression button (fx)

Next Add the below expression in the expression window

=Code.DoubleMatchFee(Fields!RunsMade.Value,Fields!WicketsTaken.Value,Fields!FeePerMatch.Value)

Click OK.
Step 3: Drag and drop the DoubleMatchFee column to the Report Designer.

Right click on the DoubleMatch Fee column and from the text box properties choose Font and let us write the below expression against the Bold Font Weight

=Code.SetBoldFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

For all other columns, let us enter the below expression against the Fill color obtained from the Text Box Properties

=Code.SetColor(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

And we are done. Let us run the report and the output is as under

Hope that we are now comfortable to work with calculated filed.