when not to use SSIS and stored procedures

Question

when not to use SSIS and stored procedures

jadelola

SSC-Addicted

Points: 493
More actions
September 13, 2011 at 2:07 am

#244867

Hi,
When will you decide not to use SSIS when having to import data?
Also, would you use stored procedures in you packages?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Not to use SSIS? When you are moving data between tables on the same server and there is little logic, and you don't need logging, event handling et cetera, I would go for stored procedures.

I also use stored procedures in my packages, as SSIS doesn't really have friendly SQL editors. So I create my SQL in SSMS, instead of in SSIS.

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

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

I also read an article not too long ago, which I cannot now find (grr!), which suggested that SSIS was the wrong tool to use in the case of "drip-feed" data - small amounts of data updating regularly and requiring frequent processing - as the overhead of firing up and executing a package is the proverbial "hammer to crack a nut" when it comes to small amounts of data.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kramaswamy SSCoach Points: 18181 More actions · Answer 3

I generally use SSIS only when the tasks either involve files which are outside of the database, IE flat files, excel files, etc ... or when the sheer amount of data is very large. SSIS is great for dealing with millions of rows of data, but is kinda a waste if you're just talking about taking a hundred rows from one table, manipulating them, and then putting them in another table.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Phil Parkin (9/14/2011)
I also read an article not too long ago, which I cannot now find (grr!), which suggested that SSIS was the wrong tool to use in the case of "drip-feed" data - small amounts of data updating regularly and requiring frequent processing - as the overhead of firing up and executing a package is the proverbial "hammer to crack a nut" when it comes to small amounts of data.

That seems an interesting article. Please search harder 😀

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

Tim Mitchell SSCoach Points: 15685 More actions · Answer 5

I almost always use SSIS for ETL processes. That said, I'll often call stored procedures from an Execute SQL task in SSIS. If the business logic I need already exists in a proc, why reinvent the wheel?

There are cases when T-SQL surpasses SSIS in its ease and performance. For example, when processing a Type 1 dimension, I generally use the T-SQL MERGE statement rather than using the SCD components (native or otherwise) in SSIS. But even in doing so, I'll wrap that statement into an SSIS package to get the benefits of logging, auditing, error handling, etc.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Phil Parkin SSC Guru Points: 246978 More actions · Answer 6

Koen Verbeeck (9/14/2011)
Phil Parkin (9/14/2011)
I also read an article not too long ago, which I cannot now find (grr!), which suggested that SSIS was the wrong tool to use in the case of "drip-feed" data - small amounts of data updating regularly and requiring frequent processing - as the overhead of firing up and executing a package is the proverbial "hammer to crack a nut" when it comes to small amounts of data.
That seems an interesting article. Please search harder 😀

Here you go.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

Phil Parkin (9/21/2011)
Koen Verbeeck (9/14/2011)
Phil Parkin (9/14/2011)
I also read an article not too long ago, which I cannot now find (grr!), which suggested that SSIS was the wrong tool to use in the case of "drip-feed" data - small amounts of data updating regularly and requiring frequent processing - as the overhead of firing up and executing a package is the proverbial "hammer to crack a nut" when it comes to small amounts of data.
That seems an interesting article. Please search harder 😀
Here you go.

That is indeed a very interesting (albeit short) article. Thanks a lot!

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