BDD: A Yes to BDD. It distributes the data to multiple paths in equal proportion. It is good because it redirects buffers to various outputs instead of redirecting an individual row. It means it works on a set of rows and it is faster. BDD is intended for parallelsism so make sure that the package is running on server with multiple cores and various disk spindles supportingn the destination database. It is more useful when the transformations are taking much time. In that case create various outputs from BDD and apply the transformations across all the BDD outputs.

MaxConcurrentExecutables (Max tasks that can be executed in parallel) is defintely a propety to tweak around in case of parallelism is needed. It depends on the number of processors on the server running the packages.

Some times it is good to have some Asynchronous component (eg: UnionAll) withing the data flow task to add execution tree or execution path so as to get more thread to work upon.

In case there are independent data flow paths inside the data flow task, engine thread property of the data flow task can be changed to gain parallelism.

Multicast component can also be used to introduce execution sub paths inside an execution path so as more parallelism can be achieved.

So, if my data volume is huge and lot of transformation components are needed without any specific ordering for the rows, I will use BDD. If my destination is not supporitng paralle load, I can merge the BDD output paths using a Union All (it will add a executuion tree also) and then do the insert into destination. If there are tasks at control flow that can be executed in paralle then MaxConcurrent executables property can be modified to gain parallelism.

"Fast processing" is a very borad-range word. In real-world scenarios, it depends on a lot of things.

The Balanced Data Distributor (BDD) is not my favorite, because it depends on too much environment issues. Just splitting data wihtout the possibility to define conditions (as a conditional split does) leaves you with either having multiple destinations that compete for IO or locks, or you have to duplicate your transformation logic, if you have such. It could, however, speed up processing if your trasnformation logic contains blocking components (e.g. script or sort components). In this case, the benefit lies in that the component will wait until the whole data has been read in, and because the data is split into parts, it will wait only a fraction of the time it dows ithout BDD.

A possible scenario where BDD would speed up your ETL is when your first data flow converts the data into something more readable for the rest of the ETL (using column conversion) and presists it into a RAW file. Here using the BDD will produce multiple RAW files that then can be precessed in parallel by multiple Data Flow tasks.

The MaxConcurrentExecutables setting can speed up your code. I tend to leave it on something like 255 and let the SSIS runtime decide how much threads it will choose.

My favorite is Conditional Split, especially if you receive data that contains data of multiple partitions of your data destination. The split will then separate data on a partition level so you can use one Data Destination per partition and will benefit from the speed-up, becuse the SQL insert operations are independent from one another (and the disk I/O hopefully is, too).

A general strategy to speed up is to avoid blocking components like Sort or Union. Thinking about your algorithm and using conditional split and Multicasts widely has always been a good idea with me.

BDD with MaxConcurrentExecutables in parellel threads