SSIS:Best Practices for Better Performance

This article intends to cover the performance improvement techniques and performance constraint scenarios based on the developer’s scope only.

Choice of Transformations

In a real world we would have to do several transformations before the data is actually loaded. The transformations use the buffer memory which in turn affects the performance.So it is very important to understand which transformations influence the performance and how

The transformations can be categorized in to Fully Blocking Transformations, Semi-Blocking Transformations and Non – Blocking transformations.

Fully Blocking Transformations: Blocks the entire dataset to perform the transformation.

Semi-Blocking: Blocks group of data to perform the transformations.

Non – Blocking: No blocking of datasets.

As a general rule, we should try to reduce the number of blocking and semi-blocking transformations.