question

Mrs_Fatherjack avatar image
Mrs_Fatherjack 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
sqlssisprocedurestored
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is the best way, +1
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ThomasRushton But if SSIS job breaks at 2:30am then you get your first cup of coffee at about 2:40 in the morning instead of waiting until the usual 7:00am :)
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.