Advantages of using SSIS packages over stored procedures?
If I can do the required ETL requirements using stored procedures, any advantages of using SSIS packages instead? My ETL stuff is nothing major.
I feel like using an old technology. I like SQL. Old technology does not equal obsolete as stored procedures won't go away any time soon.
Answer
If your ETL is mostly E and L, with very little T, and if you can write your SPs so they don't rely on cursors, then going the SP-only route is probably fine.
For more complex processes, particularly those that involve heavy transforms, slowly changing dimensions, data mining lookups, etc, SSIS has three advantages.
First, it manages memory very efficiently, which can result in big performance improvements compared to T-SQL alone.
Second, the graphical interface lets you build large, complex and reliable transforms much more easily than hand-crafted T-SQL.
And third, SSIS lets you more easily interact with additional external sources, which can be very handy for things like data cleansing.