We help IT Professionals succeed at work.

SSIS vs SPs for ETL

anushahanna
anushahanna asked
on
We have been moving lot of data using SPs, but using more SSIS now.

But at the foundational level, does SSIS use its own SPs to acheive what it is doing? Is that one way to look at it, from a lay man's perspective - instead of us coding ETL through SPs, SSIS does it itself?

thanks
Comment
Watch Question

Commented:
SSIS uses what ever means of selecting data you tell it.  So you can add SPs, SQL, etc... as a way of selecting the data to migrate.  But its design is to allow the move of data with extreme efficiency.  Not to mention its tools allow the you to do the complex with ease.  It also has easy to use logging, error handling, and debugging.

If you're doing one time migrations, you might find the SSIS wizard to your liking.
Reza RadConsultant, Trainer
Commented:
SSIS is very simpler than writing SPs for transfer data,
and also there are some times which you will find it hard to transform data as you want ( for example a fuzzy lookup is hard job to implement in SP, but is very simple thing in SSIS ) ,there are lots of samples which you can do in SSIS , but is hard in sp's. just look at many transforms you have in SSIS Data flow task.
take a look at wide range of transformations you have in SSIS:
http://msdn.microsoft.com/en-us/library/ms141713.aspx


I strongly believe that use SSIS for your ETL jobs.

Author

Commented:
Thanks. You said it well.
Reza RadConsultant, Trainer

Commented:
Glad to help,
Regards,