CODEWITHSUNDEEP
sql-serverperformancessis
Luyi
Luyi

Reputation: 23

SSIS: multiple data stream in one data flow or...?

I have a question about performance in SSIS package. In the case you have several tables to feed, from several sources, is it more efficient to put several data stream in one data flow in parallel, or to put each data stream in one data flow, linked each other sequentially ?

Thank you :)

Upvotes: 2

Views: 1281

Answers (1)

billinkc
billinkc

Reputation: 61211

Neither. ;)

I find it is better to have a data flow with as many data sources and destinations that are required to fulfill the needs but no more. If flat file 1 is the only thing needed to feed destination 1 then that is all my data flow would contain. While adding flat file 2 with a feed to destination 2 is valid for the same data flow, my rule of thumb is if the streams never cross, keep them separated.

There was a nasty bug in 2005 RTM where having multiple, unrelated flows in a single data flow lead to all the rows arriving at a destination but not all the column data once a certain data volume was met.

Another reason for separate data flows and perhaps it anecdotal but it seems that the validation is more efficient when the flows are smaller.

If you're looking to maximize performance, don't link those data flows in serial though, make them in parallel - either have them all floating on the canvas or create a central starter step (I'm fond of a do nothing Script Task) and then radiate out from that point.

Upvotes: 3

Related Questions