| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-03-16 : 09:38:51
|
| I've heard some critisism launched against DTS. But it seems much of that relates to how DTS use to be and not how it is now. I've also heard some SQL Admins insist that BCP and staging tables are better and run faster. But surely they are not faster to develop?There are many people posting threads on DTS here - so people are obvoiusly benefitting from it.How respected a tool is it really in the industry? How safe/stable is it to use? Is it a wise choice? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 10:58:31
|
| This is probably a useless answer, as I expect you have an expectation of carefully thought through development, with decent error checking; but all (well, "most" of!!) the DTS packages I've seen have been thrown together with a few RightClicks, and have been as unstable as an unstable thing.The first that our application knows that some third-party-built DTS process has failed is because there is an empty table - 'coz the bloke that wrote the DTS did "TRUNCATE TABLE" ... "COPY STUFF INTO TABLE"And our spec was "We cannot provide you with a deletions list, so if an existing record is not in our table delete it""Are you sure?""Yup"We put a check for an unreasonable change in the number of rows in the inbound table, but I sent them a bill anyway!Kristen |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-16 : 12:36:47
|
IME:We avoid them as far as possible.If we can use BULK INSERT or Linked Servers we do that instead.We find it easier to write, manage and maintain SQL code than DTS packages.We never put any business logic inside DTS packages.But it's not a bad tool, and can be slick sometimes,I just love to run dtswiz...  rockmoose |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-16 : 13:20:06
|
| DTS is a great tool. It is funny that microsoft doesn't mention DTS very much in their literature when comparing themselves to other databases. Oracle certainly doesn't ship with a tool that has this kind of capability.-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 13:53:38
|
I don't think DTS has gotten a bad rep at all, but I do know most admins don't use it because they don't need it. I don't use it myself, except to manage packages written by other people (and I'm trying to dump the ones that work with text files). As far as Microsoft is concerned, they've revamped DTS into Integration Services, which will be packaged with SQL Server 2005. That they WILL promote heavily, and it completely blows the doors off of any other product.quote:
I've also heard some SQL Admins insist that BCP and staging tables are better and run faster. But surely they are not faster to develop?
Once you do it 3 or 4 times, they are faster to develop. The fastest DTS can be is a Bulk Load task, which uses the same technology you can find in BULK INSERT, so it's never any faster than that.Now, if you never deal with text files, and have to import data from other kinds of products, DTS is da BOMB. The trick is to not use the really fancy features unless absolutely necessary, and it's extremely rare when they are. Things like data-driven tasks, dynamic lookups, ActiveX transformations, and so on. Like cursors, they are quick, dirty, and flexible, but SLOWWWWWW, and the pretty colors and graphical interface are great eye candy but quite often get in the way of getting work done. This is why most admins don't bother with it. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-16 : 14:08:47
|
| Excellent!Thanks Robvolk. That answers my question. I only have to shift data from one SQL DB to another - no text files. That's why DTS seemed the obvoius choice for me. I don't use data driven tasks or dynamic lookups. I do use Active Transforms though. But I dont have any performance constraints. So I can afford it. The only requirement is fast development. And that's why DTS is "da BOMB" for me. Looking forward to Integration Services! Where did I put my latest copy of MSDN... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-16 : 16:07:34
|
| >> I only have to shift data from one SQL DB to anotherI still would look into linked servers.>> The only requirement is fast developmentIf the structure of the tables are the same, I would just generate the SQL Could cut development time from days to hours...In SQL2005 DTS will be rewritten (Integration Services), that might be of some concern if the packages have to be ported in the future.rockmoose |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 16:24:42
|
quote:
Originally posted by robvolk
Now, if you never deal with text files, and have to import data from other kinds of products, DTS is da BOMB. The trick is to not use the really fancy features unless absolutely necessary, and it's extremely rare when they are. Things like data-driven tasks, dynamic lookups, ActiveX transformations, and so on. Like cursors, they are quick, dirty, and flexible, but SLOWWWWWW, and the pretty colors and graphical interface are great eye candy but quite often get in the way of getting work done. This is why most admins don't bother with it.
SAY AMENCan I get an AMEN?AMENAnd it's funny, because I just gave 2 consulatnats a scratch pad database, and threw profiler on to watch them...know what? All that GUI styff, is still handled the same way we do it manually...OH, and I'm sure you 'll see the work arounds M$ put in.I always though that when you changed a table in EM they used an ALTER.Pay no attention to the man with billions of dollars behind that curtainSAY IT AINT SO.....Brett8-) |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-16 : 19:39:20
|
quote:
Originally posted by X002548I always though that when you changed a table in EM they used an ALTER.
This depends. If you add a column to the middle of a table it does that whole temp table/insert/drop/rename thing that you (i think) are alluding to.If you add a column to the end of the list, it just uses an alter statement.Is that what you are talking about?-ec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 21:06:04
|
| You've lucked out with your trace Brett, they must've been doing stuff that didn't use SQL-DMO that much.If you REALLY want an education in overhead, play around with some basic DMO stuff and run Profiler when you do. Just connect to the server and enumerate the database names using VB or something like it. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-03-16 : 22:07:13
|
| IME, DTS is a cool tool, ofcourse the one that's designed (not using wizard)I've used it for archiving data from one server to another without creating a linked server, i can control how data is to processed and so onso far, it's pretty stable.--------------------keeping it simple... |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-17 : 03:33:25
|
| thanks jen, I agree.robvolk, no worries about Integration Services. This is a one-off task as part of an upgrade. The new database is quite different. I used linked servers as well.What drugs is X002548 taking? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-17 : 04:50:10
|
| "I always though that when you changed a table in EM they used an ALTER.[i]"IME only for a new column added to the end of the table (as Rob said) [i]that is NULL or NOT NULL with a defaultRenaming a column is also a minimilist manoevour.Kristen |
|
|
|
|
Position of DTS in the marketplace