This is a bit of a hybrid question between T-SQl and SSIS
I am looking at using CDC to load a data warehouse. Now with no warning of number of changes I am planning to make it as performant as possible.
I have one table with Insert / Update / Delete commands. I then plan to action these changes through SSIS. At first glance this seemed to be split the feed into three using conditional split and then to use OLE DB destination for the insert, 2 OLE DB command tasks (one for delete and one for insert). This is how MS have done it in one of their example packages.
Now I woukld like to challenge this. Surely the uuse of OLE DB command means the changes (updates and deletes) will be executed row by row?
Surely three Execute SQL TAsks would be better? Could they be faster than OLEDB Destination (Inserts), OLEDB Command (Update), OLEDB Command (Delete).
As this package will be used as a template I am sure I could make the Execute SQL task use a dynamic SQL statement.
Your thoughts much appreciated.