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. 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.
Functionality of the BDD
The Balanced Data Distributor takes advantage of parallelism to speed up data transformations, so it won’t have an effect on single-processor configurations. (In fact, it could degrade performance versus a straight insert using a Script Component in SSIS.) Microsoft recommends using this particular transform only in specific circumstances. An appropriate scenario involves all of the following criteria:
- There is a large volume of data to be moved.
- The data can be read quickly (from a flat file, for example), but there is a potential bottleneck in the transformation process or the destination.
- The order of the source data does not need to be maintained (BDD splits it into roughly equal buffers).
- The destinations should all be uniform, or of the same type.
When to use the BDD
Using the BDD requires an understanding of the hardware you will be running on, the performance of your data flow and the nature of the data involved. Therefore it won’t be for everyone, but for those who are willing to think through these things there can be significant benefits. Here is my summary description of when to use BDD:
- There is a large amount of data coming in.
- The data can be read faster than the rest of the data flow can process it, either because there is significant transformation work to do or because the destination is the bottleneck. If the destination is the bottleneck, it must be parallelizable.
- There is no ordering dependency in the data rows. For example if the data needs to stay sorted, don’t go and split it up using BDD.
For Example we can refer following link:
- MSDN Blogs
- SSIS Balanced Data Distributor
For Comparison Just visit this link:
For Download and installation this component visit following link:
- MSDN Blogs
- The SSIS Balanced Data Distributor is released « Thomas Kejser’s Database Blog
- Playing Around With the New SSIS Balanced Data Distributor Transform Component
Hope this helps !!
If you don’t get everything you want, think of the things you don’t get that you don’t want
— Oscar Wilde