Put logic inside Execute SQL Task or call sproc?

Question

Put logic inside Execute SQL Task or call sproc?

Ted Manasa

Default port

Points: 1461
More actions
September 16, 2010 at 2:09 pm

#145185

I have been using SSIS for years and have never been able to decide whether it's usually better to put SQL logic in an Execute SQL Task or create a sproc and call it from the Execute SQL Task.
I'm trying to formulate a best practice for myself.
When it comes to DDL functions, I prefer to code them directly in Execute SQL Task.
But for DML, especially when I want the database to perform the operation in a set-based manner, I am still at odds with myself.
What is your preference and why?
Thanks!
---------------------------
|Ted Pin >>

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Ted Pin (9/16/2010)
I have been using SSIS for years and have never been able to decide whether it's usually better to put SQL logic in an Execute SQL Task or create a sproc and call it from the Execute SQL Task.
I'm trying to formulate a best practice for myself.
When it comes to DDL functions, I prefer to code them directly in Execute SQL Task.
But for DML, especially when I want the database to perform the operation in a set-based manner, I am still at odds with myself.
What is your preference and why?
Thanks!

Hey Ted,

I prefer sproc for the only reason that they are more manageable and offer certain degree of flexibility wherein a complex logic is invloved...for all other scenarios it is plain T-SQL statements

Raunak J

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

My preference has, and almost always will be, SPROC if there's logic involved if I can get away with it. Primarily so that I only have to re-deploy a stored procedure instead of a package in a heavily anal QA/Prod rollout environment where I have to justify sneezing.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Craig Farrell (9/19/2010)
My preference has, and almost always will be, SPROC if there's logic involved if I can get away with it. Primarily so that I only have to re-deploy a stored procedure instead of a package in a heavily anal QA/Prod rollout environment where I have to justify sneezing.

That pretty much sums it up for me, as well. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Manasa Default port Points: 1461 More actions · Answer 4

That's the way I lean, myself, but have never been entirely confident about it.

Thanks for giving the argument some weight.

---------------------------
|Ted Pin >>

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Jeff Moden (9/19/2010)
Craig Farrell (9/19/2010)
My preference has, and almost always will be, SPROC if there's logic involved if I can get away with it. Primarily so that I only have to re-deploy a stored procedure instead of a package in a heavily anal QA/Prod rollout environment where I have to justify sneezing.
That pretty much sums it up for me, as well. 🙂

Same here with an added cmment in favor of stored procedures.

If you put the logic in a stored procedure then that logic becomes available to other process that might need it.

🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Doug Lane SSCrazy Points: 2118 More actions · Answer 6

I also favor sprocs wherever possible. Another advantage of sprocs is that you can check the task's output without having to open the package in BIDS. Otherwise, you have to go fetch the SQL out of the package to test what it does.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

In our environment they are against using stored procedures, for the simple reason that if you deploy your SSIS packages, you could forget to deploy your stored procedures. And then suddenly your packages don't work on production 🙂

I'm not entirely sure about this, but I think that a stored procedure can outperform the same T-SQL statement in an Execute SQL Task, as the stored procedure has already been compiled and possibly an execution plan has been attached. Performance experts, am I right, or am I just talking nonsense here?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

da-zero (9/20/2010)
In our environment they are against using stored procedures, for the simple reason that if you deploy your SSIS packages, you could forget to deploy your stored procedures. And then suddenly your packages don't work on production 🙂
I'm not entirely sure about this, but I think that a stored procedure can outperform the same T-SQL statement in an Execute SQL Task, as the stored procedure has already been compiled and possibly an execution plan has been attached. Performance experts, am I right, or am I just talking nonsense here?

I'm no performance expert but I'd be very surprised if the logic in the SQL task was faster than the stored precedure.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ted Manasa Default port Points: 1461 More actions · Answer 9

da-zero (9/20/2010)
In our environment they are against using stored procedures, for the simple reason that if you deploy your SSIS packages, you could forget to deploy your stored procedures. And then suddenly your packages don't work on production 🙂
I'm not entirely sure about this, but I think that a stored procedure can outperform the same T-SQL statement in an Execute SQL Task, as the stored procedure has already been compiled and possibly an execution plan has been attached. Performance experts, am I right, or am I just talking nonsense here?

There shouldn't be any perceptible difference in performance between Execute SQL and a stored proc if they are both set-based processes: The only difference could be the overhead an Execute SQL task would incur when passing the logic over the OLE DB connection to SQL Server.

That said, if the process is looped over, the stored proc should perform a bit better because the looping would occur entirely within the RDBMS engine and not between it and the SSIS package if you used Exec SQL.

Thanks for your comment!

---------------------------
|Ted Pin >>

Doug Lane SSCrazy Points: 2118 More actions · Answer 10

da-zero (9/20/2010)
In our environment they are against using stored procedures, for the simple reason that if you deploy your SSIS packages, you could forget to deploy your stored procedures. And then suddenly your packages don't work on production 🙂

When someone decides a column needs renaming, the risk of forgetting to deploy a sproc will seem minimal compared to having to open all the affected packages, edit the SQL tasks, recompile, and re-deploy. I shot myself in the foot that way once. Once was enough.

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 11

I'd agree with the sproc thing. The compile time is likely minimal and unless you are running this a lot, it won't necessarily matter.

The deployment thing is an excuse. You could easily forget to deploy anything, and code hidden in packages is hard to find. It's also not reusable in another module/package.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 12

Doug Lane (9/20/2010)
When someone decides a column needs renaming, the risk of forgetting to deploy a sproc will seem minimal compared to having to open all the affected packages, edit the SQL tasks, recompile, and re-deploy. I shot myself in the foot that way once. Once was enough.

Well, that is a good argument. I'll keep it in mind when the discussion about packages vs stored procedures rises up again. 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Steve Jones - Editor (9/20/2010)
The deployment thing is an excuse. You could easily forget to deploy anything, and code hidden in packages is hard to find. It's also not reusable in another module/package.

I know. But I'm even not allowed to use views for the same reason. They can be forgotten... :rolleyes:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 14

My vote is on using a stored proc. I find it much easier to manage any logic changes. I tend to use SSIS as a means of getting data in and out of the database. Once it's in the DB I stick with SPs.