When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
Print this page.
The SQL Guru Answers your Questions...

Can you briefly explain the advantages of using DTS for transformations versus stored procs? How about recommending some beginning / intermediate-level books on DTS development (goes beyond the 'Dummies' level). - Thanks!

Short answer: run away screaming from DTS until it's been released for 5 years.

Long answer: I'm not too fond of DTS these days. Okay, it isn't too bad if you need to shuffle a few rows from one place to another. But it seems like if you need to do anything even remotely complicated with DTS, you need to jump through a lot of hoops.

A while ago, I built a data warehouse for my company that tracks traffic on our web sites. Initially, I wrote everything with DTS, then quickly switched back to good ol' bulk copies and stored procedures. Some specific complaints were:

  • slow, slow, slow: A straight DTS copy is reasonably fast, especially if both data providers support bulk load. But if you need to do any transformations (or, heaven forbid, code lookups), DTS slows way down.
  • Hard to reuse code: ActiveX scripts - I could barely contain my glee! Until I found out that I couldn't share code between ActiveX tasks. Ick. I like to write modular code. On a barely-related topic, why can't T-SQL tasks use variables defined in the package or ActiveX scripts?
  • Poor control-of-flow: Precedence constraints are tricky to use because ALL constraints must be satisfied before a task will execute. That makes it impossible to write useful things like error handling tasks. Coupled with the lack of code reusability, this was a killer.
  • Packages stored in a proprietary format: A couple of weeks ago, I had to rebuild a SQL Server. After restoring all the databases, I had a DTS package that kept failing. I tried to open it and kept getting a "Class not registered" error. The package wouldn't open. Prior versions of the package wouldn't open. And I had no way to look at the "source code" to try and figure out what the problem was. So I deleted it, and rewrote everything in T-SQL.
  • User interface (Package Builder) is bad: It's buggy, and it will helpfully erase work for you. Be careful. (SP1 is a bit better)
  • Hard to pass parameters to a package: Oh, and let me tell you, I tried. DTSrun? Nope. sp_OACreate? Nope. Hmm. I can pass parameters to stored procedures, how convenient! I think I'll use one of those...
  • Transactionally inconsistent (?): Okay, this last one is a bit of a stretch. There were several times when I'm pretty sure DTS did not completely roll back a transaction. (Each task was instructed to join the transaction; I checked). Weird. I never did check this problem out fully, but be careful.

Okay, even though I sound a little bitter, I'm really not. ;) DTS has a lot of promise, and it is admittedly pretty cool that you can pull data from a lot of different sources. (Note: OPENROWSET/OPENQUERY works well for this also). It's nice that a package serves as sort of a big error trap for all of the tasks; T-SQL error trapping can be a bit weird/nonexistent.

So, I'd recommend using DTS if you don't have a very complicated process to do, and just want to whip something out. If you need something fast and robust, and if you have any experience at all with writing good queries T-SQL, you'll come out way ahead by using that technology.

Umm, books on DTS. Truthfully, I don't read a ton of books on SQL Server, but I'll take a peek at a few and post again when I've got some recommendations.

Hope this helps,

Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article