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
- 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.