asked
SSIS - Efficient Scripts
I'm new to working with SSIS and wondered if someone could tell me whether, when using the execute SQL task for a basic script, it's more efficient to put the script straight in the task or write to call a stored procedure? I would assume that a stored procedure is obviously easier to manage but what are the other pros and cons and what do people recommend? Many thanks
I can't say that either one would be more efficient in performance, I would assume if the TSQL is the exact same it wouldn't be any different however I typically like to put my code in stored procedures then call the stored procedure within SSIS so when I have to make a change to the business logic I don't have to redeploy the SSIS package, I simply update the stored procedure.
If you have the code in a sproc, you have a more central location for making changes (or even reusing the sproc in another package). It would also allow you to make changes without necessarily having to touch the SSIS package. Then again, if you only have the code once, it won't make a great deal of difference if it is in the SSIS package.
It depends. There, I said it. If the code you're running is only on one server/database, then you could put it in a stored procedure. If, on the other hand, it's in a module that queries multiple servers / databases, then I would definitely keep it in the SSIS project - much more manageable that way. My concern with putting this stuff into a SP is that what happens if some numpty goes and deletes the SP? You end up with SSIS jobs breaking at 2:30am, with no known reason why. I suppose the ideal world would be to have the system smart enough to be able to create its own SPs as & when required - but then you get into the aggravation of figuring out how to let the SSIS task know that the SPs have been updated.
