T-SQL Tuesday # 146: Upending Preconceived Notions
As a programming enthusiast, I have always turned my nose up at low code solutions. However, since working with SQL Server Integration Services (SSIS) my attitude on the subject has shifted. Normally, I would just use straight T-SQL to transform data. If it wasn’t possible to do all the work with T-SQL, I would use Python. That covered mostly everything ETL related. A couple of years ago our team switched over to SSIS for some of these tasks. At first I was not a fan and there are still things I don’t like about it (don’t get me started on working with excel files in SSIS). Despite these, I have learned to live with and even come to appreciate it. Here are a few reasons why:
Low Barrier To Entry
Our team is mostly made up of data analysts not programmers. For a non-coder, SSIS provides a more welcoming introduction to writing ETLs. The drag and drop tool kit allows someone with little to no programming experience to start moving data around pretty quickly. I worked with an intern over the summer who had zero experience with databases and after a couple of weeks she was making her own SSIS packages. In a short amount of time, this low barrier to entry helped expand our team’s skill set.
Automation and Scheduling
As I previously mentioned, prior to SSIS I was writing our ETL jobs in Python and using windows task scheduler to schedule them. A strict security policy would not grant me permission to run task scheduler jobs while logged out of my machine. More than a few times, a forced software update would reboot my machine, causing none of the python jobs to run. This made for many crummy days! Using the SSIS catalog along with SQL Server Agent solved this problem for me. I can sleep better knowing the server will run the jobs.
Easy To Follow
It’s fairly straight forward to look at an SSIS package and understand what it’s doing. This comes in handy. We’ve all had a hard time deciphering someone else’s work, or even our own work if it’s been a while. The visual representation of the data flow provided by SSIS is clear and logical. When debugging a package, the failing component will have a red ‘x‘ on it to let you know it failed. This visual aid is appreciated.
In Conclusion
SSIS is not my favorite thing and I can assure you, IT NEVER WILL BE! Yet, that doesn’t mean that it’s not useful. Our team has gotten great use out of it over the past couple of years. It’s solved a few problems for us and at the end of the day, that’s what a good tool does.
