SSIS – Non-blocking, Semi-blocking and Fully-blocking components

Synchronous vs Asynchronous

The SSIS dataflow contain three types of transformations. They can be non-blocking, semi-blocking or fully-blocking. Before I explain how you can recognize these types and what their properties are its important to know that all the dataflow components can be categorized to be either synchronous or asynchronous.

·         Synchronous components The output of an synchronous component uses the same buffer as the input. Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input. Number of records IN == Number of records OUT.

·         Asynchronous components The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records then input records.

The only thing you need to remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that need a new buffer.

All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output. All destination adapters on the other hand, are synchronous.


Non-blocking, Semi-blocking and Fully-blocking

In the table below the differences between the three transformation types are summarized. As you can see it’s not that hard to identify the three types.
On the internet are a lot of large and complicated articles about this subject, but I think it’s enough to look at the core differences between the three types to understand their working and (dis)advantages:

	Non-blocking	Semi-blocking	Fully-blocking
Synchronous or asynchronous	Synchronous	Asynchronous	Asynchronous
Number of rows in == number of rows out	True	Usually False	Usually False
Must read all input before they can output	False	False	True
New buffer created?	False	True	True
New thread created?	False	Usually True	True

All SSIS transformations categorized:

Non-Blocking transformations	Semi-blocking transformations	Blocking transformations
Audit	Data Mining Query	Aggregate
Character Map	Merge	Fuzzy Grouping
Conditional Split	Merge Join	Fuzzy Lookup
Copy Column	Pivot	Row Sampling
Data Conversion	Unpivot	Sort
Derived Column	Term Lookup	Term Extraction
Lookup	Union All
Multicast
Percent Sampling
Row Count
Script Component
Export Column
Import Column
Slowly Changing Dimension
OLE DB Command

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.

Script Component Task

Introduction

Script component is a SSIS transformation component whose task is to run custom script code. Many times it happens that for some situation we do not have a built in transformation component; however, we can do so by writing some code snippet for the needed transformation. Script component come into play in such situations.

Background

Transformation is an integral part of most of the SSIS life cyle. Once the raw data comes to our hand, it is responsibility of the transformation components to make the needed morphisms and bring the data in the needed format. Though various kind of transformations are available in SSIS, but some time we need to have some kind of custom transformation for which no component is available. We can either use Script component in such situations or make our own custom component. In this article, we will look into the script component transformation into action by using two real time examples while usage of custom component will be discussed in another article.

Example 1: A String Splitter Program in SSIS using Script Component Transformation.

Context

In this program we will read the file contents which is given as under

 Collapse | Copy Code

Id Value
1 Name1, Name2, Name3
2 Name4, Name5, Name1

and by using Script Component transformation we will bring the below output

Step to be carried out

Step 1:

Open Bids.Choose Integration Services Project from the available project type.Drag and drop a Dataflow Task in the control flow designer.

Drag and drop Flat File Source in the Data Flow designer.Right click on the Flat File Source component and from the popup, click Edit… to bring the Flat File Source Editor. Alternatively we can double click on the Flat File Source component for bringing up the Flat File Source Editor. In the connection manager of the Flat File Source Editor, click on the New button and specify the source file

In the Columns tab, the Row delimiter should be {CR}{LF} while the Columns delimiter should be Tab {t}

Click OK button.

Step 2:

Add a Script Component Transformation and set it as transformation.

Step 3:

Add precedence constraint from Flat file Source to the Script component.Right click on Script component and from the popup, click Edit… to bring up the Script Transformation Editor.

Step 4: Configuring the script component

In the Input Columns tab, add the two available columns: Column 0, Column 1

In Input and Outputs tab, select the Output 0, and rename it as Result.Set the SynchronousInputID property toNone which will rather change the script component to asynchronous.

N.B. ~ there are two types of transformation in SSIS.

Synchronous Transformation

The output is synchronized with the input and the input data will be processed on a row by row basis.

Asynchronous Transformation

The output is not synchronized with the input. All the input data will be fetched initially, then all the rows will be read and followed by the output generation.

Add the below output columns under Result

Column name	DataType
ID	string [DT_STR]
CustomerName	string [DT_STR]

In Script tab, set Script Language as Microsoft Visual C# 2008, and let's click on Edit Script button

Override the Input0_ProcessInput method to fetch all data till end of file as below:

 Collapse | Copy Code

public override void Input0_ProcessInput(Input0Buffer Buffer)
    {
        while (Buffer.NextRow())
        {
            Input0_ProcessInputRow(Buffer);
        }

        if (Buffer.EndOfRowset())
        {
            ResultBuffer.SetEndOfRowset();
        }
    }

Next we need to override the Input0_ProcessInputRow method to add new rows to output as below:

 Collapse | Copy Code

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
      var arr = Row.Column1.Split(','); // Splitting the rows of Names column

      foreach (string str in arr)
      {
         ResultBuffer.AddRow(); //Adding rows to the Result Buffer

         //If the Names are not empty or Null, then set the values to the  
         //  corresponding Result Buffer properties
            if (!string.IsNullOrEmpty(Row.Column1)) 
            {
                ResultBuffer.ID = Row.Column0;
                ResultBuffer.CustomerName = str;
            }
        }
    }

Build the application and close it.

Step 5:

Add a Row Sampling and enable the data viewer. The final package design looks as under

Let us now run the package and we will the below output

SSIS architecture

SSIS is a component of SQL Server 2005/2008 and is the successor of DTS (Data Transformation Services) which formed part of SQL Server 7.0/2000. From an end-user perspective DTS and SSIS appear similar, however they are quite different. SSIS has been completely written from the scratch (it is a new enterprise ETL product) and overcomes several limitations of DTS. Though the list of differences between DTS and SSIS is quite large, one thing to note is the internal architecture of SSIS is completely different from DTS. It has segregated the Data Flow Engine from the Control Flow Engine or SSIS Runtime Engine; designed to achieve a high degree of parallelism and improve the overall performance (see the architecture image below).

 
The SSIS architecture consists of two main components as given below:

SSIS Runtime Engine – The SSIS runtime engine handles the control flow of a package. It saves the layout of packages, runs packages and provides support for logging, breakpoints, configuration, connections and transactions. The run-time engine is a parallel control flow engine that coordinates the execution of tasks or units of work within SSIS and manages the engine threads that carry out those tasks.

The SSIS runtime engine executes the tasks inside a package in an orderly fashion. When the runtime engine encounters a data flow task in a package during execution it creates a data flow pipeline and lets that data flow task run in the pipeline.

Note:
The Integration Services service (a windows service) is not the same as the SSISruntime engine/service. It is not required if only the design and execute Integration Services packages are wanted. This windows service can be started to manage SSIS packages, for example to connect to multiple SSIS servers, start/stop package remotely/locally, manage the package store, import/export packages etc.

SSIS Data Flow Engine/Pipeline – SSIS Data Flow Engine or Data Flow Pipeline or Transformation pipeline engine manages the flow of data from data sources, through transformations, and on to destination targets. When the Data Flow task executes, the SSIS data flow engine extracts data from one or more data sources, performs any necessary transformations on the extracted data and then delivers the data to one or more destinations.

The Data flow engine is buffer oriented architecture (more details will be discussed in a later section), it pulls data from the source and stores it in a buffer (memory structure) and does the transformation in buffer/memory itself instead of processing on a row-by-row basis. The benefit of this in-memory processing is that processing is much faster as there is no need to physically copy/stage the data at each step of the data integration; the data flow engine manipulates data as it is transferred from source to destination.

Unpivot Transformation

Using this transformation we can convert the data from normalized format to denormalized format i.e. we can convert the columns into rows.

Source file:

Id	Name	Jan	Feb	Mar	Apr	may
100	ravi	10000	12000	12000	15000	15000
101	rajesh	15000	17000	17000	20000	20000
102	raj	15000	15000	18000	18000	20000

Targets file Structure:

                      Id                 integer

                      Name           varchar

                      Salary           integer

                      Month name   varchar

Step1: add Data flow task to control flow—click on edit

Step2: Create connection for flat file and target database

Step3: drag flat file source and configure the flat file source.

          Convert the data types if required at source level.

Step4: drag Unpivot transformation –right click on Unpivot and click on edit

           Select the column check boxes which we are going to convert into records.

           And give the destination column name as Salary, Pivot key value name is   

           Month name

If u execute the the output is:

Id	Name	Salary	Month name
100	ravi	10000	Jan
100	ravi	12000	Feb
100	ravi	12000	Mar
100	ravi	15000	Apr
100	ravi	15000	may
101	rajesh	15000	Jan
101	rajesh	17000	Feb
101	rajesh	17000	Mar
101	rajesh	20000	Apr
101	rajesh	20000	Jan
102	raj	15000	Jan
102	raj	15000	Feb
102	raj	18000	Mar
102	raj	18000	Apr
102	raj	20000	may