Stuck Using Antiquated Technology? Use It Wisely and Plan Ahead...My DTS Story

OK, so, I was forced into creating DTS packages to use on a legacy system that I know darn well we are going to upgrade in the next couple years at some point. The boss' reasons were sound, from his vantage point, at this juncture we didn't have resources to learn SSiS, or upgrade from our current database, SQL Server 2000, I get it, so, I have to get over my personal opinion and do what I am told to do.

Having been a long time since I messed with DTS, as well as SQL Server, really, so I had a couple hours of poking around to do, to kind of familiarize myself with it again. DTS is quite simple in it's most basic level, what makes it difficult to migrate off of is that some folks use it wrong (in my opinion) by living totally within it and not relying on other tools within SQL Server, like views, stored procedures and user defined functions. Using such things make the DTS a bit more portable, and you can then use the DTS, more than anything, just as a framework with which to schedule the tasks to be run, and not have the tasks hold the business logic itself.

So, that is what I decided to do with this project, all the business logic I needed, that was able to be, I wrote into large queries and created stored procedures with them, so when I needed those steps to be done, the DTS steps simply called the stored procedure, and had no specific SQL in the step itself.

The only thing I couldn't really handle that way was an initial step of importing data from an outside data source, in this case it was a dBase 5 file, that is created by another in house data manipulation/cleansing application, for that I needed only one simple data transformation step to throw that data, as it is, into a matching table in SQL Server, so it isn't really transforming anything, just moving it as it. From that point everything is done within SQL Server so it's not spending time looping through an open *.dbf file, which increased the speed, and if something does fail, it is handled within the following stored procedures and logged in an error logging table.

Over all, I am hoping, once it's time to upgrade away from SQL Server 2000 and we have the resources to move to SSiS or other platform, creating these packages in this manner will make the move much less painful and time consuming.

Any other thoughts and I ideas are welcomed in the comments.