Improve ETL performance in SSIS by introducing parallelism in the data flow of an SSIS package.
There is a new transform component available for SQL Server Integration Services. It’s called the Balanced Data Distributor (BDD) and the download is available here.
Note
Downloading the BDD transform can be a bit tricky as when you go to the above site it will only have a link to a word document. Please download this document and read the instructions, at the bottom of it is a button with “I Agree”, hover your mouse over it and use Ctrl + your left mouse click to open the link to download the actual msi package that contains the BDD component. (its bit of an IQ test )
The BDD provides an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package.This diagram gives a trivial example:
If you run this data flow on a laptop, there probably won’t be any speed advantage, and there may even be a speed cost. But suppose you run this on a server with multiple cores and many disk spindles supporting the destination database. Then there might be a substantial speed advantage to using this data flow.
For more information read the SQL Server Performance Teams blog article on this component - https://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx
Enjoy speeding up the data refreshes to your data warehouse.