Stored proc vs ssis

[mflancour]

I have only started researching ssis, but know stored procs pretty well. My question is, is there any reason to use a ssis project over a stored proc? as far as I can tell everything that can be done with ssis can be done more easily with a stored proc.

 

[AlexCuse]

I think you need to do some more looking at SSIS. While you can work with text files and such using stored procs and openrowset/bcp, SSIS will give you much more control and a more flexible solution.

Which you should use is dependent upon the task, and without knowing what that is, no one can really answer your question.

Ignorance of certain subjects is a great part of wisdom

 

[mflancour]

Nothing specific that I'm trying to do. Just looking into ssis and seems pointless to go farther if i dont need to. Can you give me an example of something that can be done with ssis and not with a stored proc?

 

[AlexCuse]

I am reminded of an answer from another newsgroup.

It went something like this -

Yes, you CAN do it with T-SQL, but just because you CAN does not mean you SHOULD.

Some things that I use DTS (2000 equivalent of SSIS) exclusively for include file and directory manipulation, large(repeated) data transfers where data needs to be cleaned/modified as it is imported/exported, anything involving FTP'ing of files, anything where use of a language that is more conducive to looping would be beneficial, anything with so many steps that to read it in one stored proc would be a nightmare, etc... They are good for anything that needs to interface with a different database platform as well. I prefer the way they interact with front end languages as well, as it can get tedious building an SQL string for a sp with many many parameters.


With the addition of CLR stored procs and functions in SQL 2005, there is more you can do with T-SQL (if you have the option of extending the language in this way). However, when we go to 2005 here at work and I have the option, I think I will actually be using SSIS for more tasks than I currently am because of the move to vb.net or C# from the old vbScript.

So still, without knowing your situation I can't tell you what is best to do. I will say that if you have the opportunity to learn SSIS, then why not? They are fairly easy to learn, very powerful, and a valuable skill to have in the job market.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mflancour]

Ya, you are right, of course. Just getting frustrated. DTS was real easy for me to pick up because I had tons of examples already. So far trying to lean ssis has been pretty frustrating. Everyone says rtfm, but I've never been able to learn from manuals, so I'm sitting here spending forever trying to find examples then I find one or 2 and have no idea how to implement them so I can figure out how they work. Well, never mind my venting, guess I'll go through more tutorials ...sigh

 

[AlexCuse]

I am finding the move to SSIS frustrating as well, but I have seen a few examples of how much easier/better they make things (vs the 'old' way), and I must say I'm sold on them.

Have you checked out this website:

http://www.sqlis.com/

I learned a fantastic amount from their DTS counterpart.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom