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.
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,
Sean
| Read Other SQL Guru Questions |
