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:
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.
We have developed an access tool which allows to run DTS packages on SQL Server 2012.
Is anybody interested?
I would like to know if it makes sense to market this solution.
Please, leave me a message at