Choosing the right tool
I once heard someone say:
if you use SSIS to do ETL, you are just not a good enough programmer in SQL.
Needless to say I had some thoughts about that! We had a discussion about ETL and what tool is the most suited for that task. In the end it came down to the fact that he was better at coding SQL.
There are a lot of reasons why you may prefer one tool over the other. But if your main reason is "I am better experienced with tool A than B", then I wonder if that is a good reason enough on it's own.
I prefer to first look at the right tool for the job, regardless of who is going to do it eventually.
Sure, if tool A is better for the job than tool B - and no one can learn tool A in time, and tool B does the work just fine - then sure, go for it. But the point is: be aware why you chose the tool to do the job. You shouldn't be blinded by your own preference. When I ask why a tool was chosen, I often get this reason.
So the original question was: which is better? SSIS or SQL for ETL? Here's my two(3) cents:
- regardless of the decision, use SSIS to fire off SQL if you choose SQL over dataflows. You keep the visual overview and the ability to run stored procedures in parallel.
- loading dimensions parallel is a sure time saver. Not so easy to do in a CSP.
- It is much easier to change a source or a destination connection string in a SSIS project.