Batch SQL vs. SSIS Data Flow row by row
-
Hey Gang,
I'm experience cognitive dissonance over whether to use SQL batch updates in SQL tasks or to fully convert them to SIIS data flows.
The wrox book gives conflicting advice. In an early chapter they state a caveat that data flow processes are row by row and one should consider carefully if that's an advantage over using SQL batch updates before translating them into data flows. Then later on in the book they advise totally divorcing your package from the RDBMS and translating SQL batch statements into data flows with lookups.
I have a functional SQL sproc based import but it needs to be more extensible so it can include many more heterogeneous sources. Currently I've started by just translating the various SQL statements into expressions in SQL tasks thinking the batch attack would always perform better. But the conflicting advice, plus the fact that error handling seems *very* limited on the Task plain are making me re-think it and maybe I should beg for more time to convert everything to data flows.
I'd really like to know what you think. When the experts give opposite advice in the same book I get that deer in the headlights look.
much appreciated!
Skål - jh
-
Hi,
I guess its a matter of opinion and the best answer anyone can give is "It depends". However, I've posted a list of things to consider up here: http://blogs.conchango.com/jamiethomson/archive/2006/03/14/3080.aspx
Note, I don't want that to come across as a recommendation either way
HTH.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I appreciate your neutrality Jamie, thanks so much for the additional info!
Skål - jh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply