Stored procedure vs embedded SQL in SSIS performance

Question

I recently completed a SSIS course.

One of the piece of best practice I came away with, was to ALWAYS use stored procedures in data flow tasks in SSIS.

I guess there is an argument around security, however the tutor said that as the stored procedures performed all of the work "native" on the SQL Server there was/is a significant performance boost.

Is there any truth to this or articles that debate the point?

Thanks

Answer 1

Remember - mostly courses are done by clueless people because people with knowledge earn money doing consulting which pays a LOT better than training. Most trainers live in a glass house that never spends 9 months working on a 21tb data warehouse ;)

This is wrong. Point.

It only makes sense when the SQL Statement does not pull data out of the database - for example merging tables etc.

Otherwise it is a question of how smart you set up the SSIS side. SSIS can write data not using SQL, using bulk copy mechanisms. SSIS is a lot more flexible, and if you pull data from a remote database then the argument of not leaving the database (i.e. processing native) is a stupid point to make. When I copy data from SQL Server A to SQL Server B, a SP on B can not process he data from A native.

In general, it is only faster when you take data FROM A and push it TO A and all the processing can be done in a simple SP - which is a degenerate edge case (i.e. a simplistic one).

The advantage of SSIS is the flexibility of processing data in an environment designed for data flow, which in many cases is needed in the project and doing that in stored procedures would turn nightmare.

Answer 2

Old thread, but a pertinent topic.

For a data source connection, I favor SPs over embedded queries when A) the logic is simple enough to be handled in both ways, and B) the support of the SP is easier than working with the package. I haven't found much, if any, difference in performance for the data source if the SP returns a fairly straighforward result set.

Our shop has a more involved deploy process for packages, which makes SPs a preferred source.

I have not found very many applications for a SP being a data destination, except maybe an occasional logging SP call.