Features in SSIS 2016

Following are the most important features in SSIS 2016:

  • Retrieving Error Column
  • Control Flow Template
  • AutoAdjustBufferSize Property

Retrieving Error Column:
In previous versions of SSIS, if a Data Flow Task Fails we don’t have option to retrieve Error Column name but we can retrieve error description. To retrieve the error column name, a new SSIS library called ‘GetIdentificationStringByID’ has been added in SSIS 2016.

Steps to get error column name:

  • To capture error column, configure the task in Data Flow to redirect rows on error.
  • Add script component as Transformation in the error path of the task. Choose “ErrorCode” and “ErrorColumn” Columns as input

Create an output column to capture error column name

  • In “ProcessInputRow” method of input buffer in Script task, give the below code:    Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByID(Row.ErrorColumn);

  • Add the derived column from script component to capture the error column name into error log table.

Control flow template:
In most of the projects, we are using the same template by copying the same structure. But to arrange it is difficult. In SSIS 2016, a new option called Control flow template has been added to the SSIS toolbox and Solution explorer as shown below

When we right-click Control flow template in Solution explorer, we will get two options as below

Once we create the new template, then a file with the extension .dtsxt is added to the control flow template.

Note: We should place all the tasks (like Execute SQL task, Data flow task) in control flow under one single Sequence container else it will throw the error.

To use an existing template just simply drag and drop it into designer surface. Templates are indicated by “T” in the upper right corner to indicate it is a reusable template. Once the template is added to the package, the tasks inside the template can’t be modified.

The AutoAdjustBufferSize Property

To optimize the package execution, at times we may need to adjust the buffer size of the task. Size of the buffer is determined by ‘DefaultBufferMaxRows’ and ‘DefaultBufferSize’ which has default values of 10000 rows and 10 MB respectively. This value is very less compared to the amount of memory, modern servers has. So these 2 property values need to manually changed whenever package is developed.

To eliminate this manual work, a new option “AutoAdjustBufferSize” has been added to the data flow properties under Misc section in SSIS 2016

If this property is set to true, then the data flow will automatically determine the buffer size. The values set in the other two buffer size properties are not considered.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s