Why should we migrate our ETL from DTS to SSIS?

I know I just threw a bunch of technical acronyms at you at once, but this is a topic that comes up more and more as people finally move off of SQL 2000 and onto a currently supported version.  Companies making this move will have a lot of old Data Transformation Service (DTS) packages that move data into their database or out of their database (Exchange, Transform and Load or ETL).  These packages have been around for years, and no one wants to fix something that isn’t broken.

But as soon as you upgrade to 2008 or later, DTS packages can break.

Limited DTS Functionality on 64-bit Operating Systems
When is the last time you bought a server with a 32-bit processor?  It’s been a while hasn’t it?  Beginning with SQL 2008, you can only run DTS Packages (or SSIS Packages that are just wrappers for DTS packages) in 32-bit mode.  While not a show stopper, you could experience some performance implications of limiting your ETL processes to 32-bit performance.  You will be limited in the amount of memory your package can access if you use 32-bit mode, and when dealing with a lot of data, the more memory you have, the better your package will respond.

Design Support for DTS packages is non-existent in SQL 2008+
If you need to maintain your DTS packages after installing SQL 2008 or later you’re going to have to install SQL Server 2005 on a development machine, and then install an extra feature pack for that instance so you’ll be able to work on those packages.  Kind of odd that you’d need to keep SQL Server around in order to support a later edition of SQL, wouldn’t you say?

Speaking of designing and maintaining DTS packages going forward…

There are fewer database professionals with DTS than there were last year
One of the cool analytics LinkedIn provides is insight into skills that are becoming more or less popular in the work force.  At the beginning of this year, linked in announced their findings for the year 2001.  There was a 19% decrease in the number of professionals listing DTS.  There are currently just over 18,000 professionals in LinkedIn who can work on your DTS packages.  Now that Microsoft has officially ended support for DTS packages as of SQL 2012, that number can only drop. As those numbers drop, supply and demand dictates you’re going to have to pay more and more for those professionals who remain.

Why not let Hive Mind Consulting help you convert your ETL processes to SSIS?
We can help you realize performance gains by using your servers’ full capacities.  We can help you design and maintain newer SSIS packages to replace your current DTS packages.  We can provide training to your staff on how to support and develop these new packages.  We also can provide you a framework to use to simplify your conversion process!  Call us today… we can help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *