Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2010-04-07 : 08:35:41
|
| My task was to move the contents of a table from a prod server to a dev server once a week. I decided the best way to do this was to use an SSIS package through the import/export wizard, schedule the job to run once a week, and we're done. My solution took 3 entire minutes to build. (Hit a snag because the servers were different versions but that will be fixed soon.)Meanwhile my coworker took about an hour and created a stored proc that dynamically looks at each table, inserts, updates or deletes rows of data based on the changes. His sp is about 7000 lines long (the sp was created dynamically using another sp).I presented my solution to him and he said "why is ssis better than SPs?" I didn't have a solid answer other than the time and effort involved. Can anyone provide a little bit more of a technical answer?Thanks!Craig Greenwood |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-07 : 09:07:21
|
It is a matter of taste!I would prefer a stored procedure because only a few people in our company are able to fix problems in SSIS but most of them are able to understand SPs and how to fix any problems. (So it is not a problem for me to go and have a holiday  )And I have seen a few years ago that using SP and linked server was much faster than SSIS for doing this kind of moving contents.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-07 : 09:12:20
|
Oh!Sorry for NOTexplaining why SSIS package is better than sp.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-07 : 09:14:49
|
quote:
Originally posted by craigwgI presented my solution to him and he said "why is ssis better than SPs?" I didn't have a solid answer other than the time and effort involved. Can anyone provide a little bit more of a technical answer?
I think you hit the nail on the head. 3 minutes Vs an hour to do the same task.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
|
|
|
Help explaining why SSIS package is better than sp