We help IT Professionals succeed at work.

SSIS vs SPs for ETL

anushahanna asked
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?

Watch Question

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
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:

I strongly believe that use SSIS for your ETL jobs.


Thanks. You said it well.
Reza RadConsultant, Trainer

Glad to help,