Execute SQ... Procedure Task

I’ve been having a bit of a debate with some colleagues today about the Execute SQL Procedure Task in SSIS. Is it ok to enter raw SQL queries into this task or should everything be enclosed within a Stored Procedure? My view…

For me, any day of the week, it should always be a proc. Why on earth would you want to enter raw queries into this task? I would argue against this even for basic message logging. Enclosing your TSQL in stored procedures gives you much more flexibility. It’s so much easier to make minor modifications to a proc. Would you rather have to go through the rigmarole of opening and deploying SSIS packages. Sure, I know you’ll have to open the package if you introduce, or remove, new columns. But you’re pretty much free to modify your WHERE without issue. Lets not forget this also allows your system to be modified by those who don’t know SSIS. Perhaps you’ll be able to enjoy your holiday a little better next year.

I’m even tempted to open a Microsoft Connect item to fix this ‘bug’. Perhaps one day I will be lucky enough to see this task available in my toolbox.