Converting Users from SSIS to T-SQL For ETL

I’ve spent a little over 9 years now working with T-SQL and SSIS, and I’ve lived through the following headaches known with SSIS:

  • Upgrading hundreds of packages when we upgrade SQL Server
  • Updating connection passwords when we have to refresh for security reasons
  • Dealing with user key encrypted packages and having to get those solved so that we can use and diagnose a problematic package
  • Documenting the contents of an SSIS package when a business user requests information

I could go on about listing all the other known issues with SSIS. I’m not bashing the product. It is by far one of the most fascinating and creative pieces of software I have ever seen. The internals of it that deal with memory buffers and multi-threading make it world class ETL software. But with all the headaches I had dealt with in the past, I knew there could be something easier and faster to work with.

At the end of 2013, I started building out a concept plug-n-play software for ETL that allows the end user to just use T-SQL to automate the creation and execution of an SSIS package. This would later evolve into EDIS, but at the time, I was just trying to come up with a solution that allowed me and my co-workers to build and deploy ETL faster and easier. After working through several revisions, I finally had a solution that used just parameters to build an SSIS package on the fly without the end user ever needing to open SSIS. Once I put this in front of some die-hard SSIS users, they were shocked and fascinated by how easy it was to use this new approach (in just 4 parameters, you could have a data flow package built and executed). One of them had been using SSIS for over 12 years (back when it was known as DTS from the SQL 2000 days). He said at first that he could never get used to this approach of building ETL workflows from T-SQL, but once he used the program for a couple days, he came back to me and said I needed to expand the capabilities further so that we didn’t have to create SSIS packages ever again.

Now, I feel that EDIS has matured to a product that is starting the optimization phase. It now covers data flows, FTP and S-FTP, web requests, local directory operations, zipping and unzipping files, and the capabilities continue to grow over time.

Another added benefit of EDIS is the self-documenting audit trail. For once, a DBA can go to just a single table to see all ETL that is being done on their server, and the mysterious black box of an SSIS package is now a thing of the past. This has been a huge win for large organizations that have ETL pulling from hundreds of sources. The DBA now has the ability to quickly answer the WHO, WHAT, WHEN, and WHERE.

All in all, this shift of moving developers from SSIS to EDIS is not easy. At first, most will look at the tool and say “so what? I can do this in SSIS. Why should I change?”. Once they realize how much faster they can build and update ETL, the fact that they only have to set credentials once and ditch config files, and that they only need to use SSMS to develop new ETL and not have to install SSDT/Visual Studio, that’s when they get the “ah-ha” moment, and say they don’t want to go back to SSIS ever again.

I’m not trying to use this blog as a sales pitch; this was more to just get down on paper the challenges and why I built EDIS. It has made managing my ETL team’s portfolio much easier and has empowered both small and large organizations the ability to train users quickly and easily so that they can get data into SQL Server much faster than they are used to.

 

 

Advertisements
Converting Users from SSIS to T-SQL For ETL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s