Thursday, May 5, 2011

Sequence Container in SSIS - Different ways of using Sequence Containers

Integration Services includes the Sequence container, which makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit.
Using Sequence containers lets you handle the control flow in a package in more detail, without having to manage individual tasks and containers.
If a package has many tasks, it can be helpful to group the tasks in Sequence containers.

I'll explain different scenarios where Sequence Container paly an important role to implement business rules.

Scenario 1:
  • Sequence Task SC1 must execute first. SC1 may have many task with or without precedence constraints. The group of these task must execute prior to any other tasks in the package.
  • Sequence Task SC2 must start after completion of SC1. SC2 must execute irrespective of Failure/Success of SC1.
  • Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by SC2. SC3 must execute only after successfully execution of SC2. 

Scenario 2:

  • Sequence Task SC1 must execute first.
  • Sequence Task SC2 must start based on logical condition. This condition can be based on package variables. 
  • Sequence Task SC3 contains Maintenence Plan Tasks and should be followed by both SC1 and SC2. SC3 must execute after successfully execution of SC1. SC3 should execute regardless of SC2 but if SC2 executes, SC3 must execute after SC2.

  Scenario 3: This is real scenario in many BI applications.

  • SC1 must execute prior to other task. This Sequence Container may contain many tasks to pull data from hetrogenious data sources.
  • SC2 should be followed by SC1. This can be used to Insert/Update dimension tables and fact tables along with intermediate tables (if any). This container may further contain Sub - Sequence Containers e.g. SC2(a) to handle dimension Insert/Update and SC2(b) to update Intermediate and Fact tables.
  • SC3 should be followed by SC2. This may contains SSAS Tasks to Build/Prcoess Cubes. This container may also have Sub - Sequence Containers e.g. SC3(a) to check whether Current Month partition exists or not. If Yes than Do Nothing Else Create Current Month Partitions using Script task. SC3(b) can be used to Process Dimensions and Current Month Partitions.
  • SC4 can be used for Maintenence Plan tasks. If SC3 successfully completed (Evolution operation - Expression and Constraint) than take Backup of Cube, Shrink Datamart and Take Backup of Datamart.
  • SC5 can be used for Recovery Paln. If SC3 fails, Restore Cube from previos & Latest Backup available..


However, there are many more ways of utilizing Sequence Containers. Further, things depends on complexity of business requirement rules defined.

Tuesday, March 15, 2011

Foreach Loop Enumerators

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

SSIS provides the following enumerator types:



  • Foreach File Enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the folder and its subfolders.
  • Foreach Item Enumerator to enumerate items that are collections. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
  • Foreach ADO Enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.
  • Foreach ADO.NET Schema Rowset Enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables of your SQL Server database.
  • Foreach From Variable Enumerator to enumerate the object that a specified variable contains. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of Servers.
  • Foreach Nodelist Enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the products of Electronics type: /products/product[@type='Electronics'].
  • Foreach SMO Enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database.

Saturday, January 1, 2011

Adding Custom Color in SSRS Chart Reports

Do you ever come across a requirement where you need to use user defined colors instead of default colors in a chart (for example Pie Chart).

In this article, I'll explain how to create a Custom Palette and how to use that palette in a Pie Chart.

In the above screen-shot, I am using color Blue, Red, Teal and Gold for the first four values of the Period column and same colors are used in Pie Chart as well.

Here are the steps to achieve this task:

STEP1:
Create your custome color palette in the report using Custom Code in your report. To do so, click Report => Report Properties => Code and copy below code:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
      Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

You can define your own colors in the above function.

STEP2:
In the Pie Chart, select Series Properties and select the Fill option from left side as shown below:



Now write following expression in the Color expression:

=code.GetColor(Fields!Year.Value)

Here Fields!Year.Value is a field of dataset which is used as Chart Category fields.

STEP3:
In the left most column of the tabular report, insert character █ and in the color property of the textbox write following expression:

=code.GetColor(Fields!Year.Value)

Here I am considing that tabular report is grouped on Fields!Year.Value field.




Thats it. Click on the preview and enjoy your custom colors in charting.

Cheers!!!