Menu
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.
794
One other key difference is that Stored Procedures are stored inside a database whereas SSIS is a service that runs on a SQL server - SSIS packages are run by the SSIS service but can be stored on the SQL server or on a file system folder somewhere else.
Benefits: The process of developing ETL based solutions is generally faster than other methods. It automates the process of data loading into your data warehouse or operational system. It gives much better performance in loading and transformation process than hand-coded or manual solutions.
Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.
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.
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
