DTS RIP

  • Article

Data Transformation Services is the bit of SQL Server that helped pay for my house, car and some really nice holidays.  I first got to use it some 12 years ago and after working with BCP (bulk copy program) it was just so easy, I was sucking data out of AS400/RPG, Oracle and Sybase to create data warehouses in weeks rather than months. However to be fair it did have its faults,  but for a free utility bundled with SQL Server (7 & later) there was no comparison.

However those faults became more important as it was used more widely and it lacked many of the capabilities of the standalone tools that were also around at the time:

  • It didn’t really have many good transformation tools so you either wrote your own in ActiveX or you did the work in views/ procedures on your target database.  This generally resulted in lots of temporary tables in your data warehouse and lots of disk activity.
  • Flow control was pretty basic, for example if you wanted to pick up and load all the excel files in a folder, you would have to do that in code yourself.
  • Parameter passing was pretty basic if memory serves.
  • The moving form development  to test, to production was tricky because data sources were fiddly to manage.  In fact I remember embarrassing myself by issuing a truncate table statement on the source instead of the target at one client site, fortunately they had backups! 

DTS Packages like this can be really hard to understand (thanks to Neeraj Nagpal for the screenshot)

There was no easy way to modify DTS to add these capabilities and so SQL Server Integration Services (SSIS) came out with SQL Server 20005 late in 2005.  However You could still run those old DTS packages  inside SSIS and even edit them if you needed to (details are here for running DTS in SQL Server 2008 R2).  This side by side capability continued in SQL Server 2008 & R2 but DTS was  specified as a deprecated feature in SQL Server 2008 and this is advanced warning that running DTS packages won’t be supported in the next version.  That next version SQL Server 2012 is now out in beta  and as stated there is no support for DTS in it.   

I first wrote a post about this problem nearly four years ago,  and my advice at the time was to do a gradual migration from DTS to SSIS where when a significant change was needed to a DTS package you would reengineer it in SSIS.  Another option is to use DTS xChange from Pragmatic Works which does cost money but makes a very professional job of automating the conversion into a well designed SSIS package with good design and proper logging. Finally you could just get in some data warehousing experts and they’ll do the work for you.

Whatever you decide DTS is pretty nearly dead, and while I do have a soft spot for it, once I learnt SSIS I realised how much was missing in DTS.