DTS is rubbish

I'm gonna say something slightly controversial just to get a bit of debate
going, but I think DTS is just a drag and drop wizard for programmers who
don't know what they're doing. As with any wizard, it can take you so far,
but if you need anything complicated it's an ActiveX script. You do not use
VBScript (or a n other scripting language) to work with large volumes of
data.
Discuss? ; )
I'm personally a big fan of bcp and BULK INSERT; there is nothing faster for
shifting data.
I guess DTS might be ok for small import jobs, and the workflow could be
useful ...
Let me know what you think.
DamienDamien <Damien@.discussions.microsoft.com> wrote:
> I'm gonna say something slightly controversial just to get a bit of
> debate going, but I think DTS is just a drag and drop wizard for
> programmers who don't know what they're doing. As with any wizard,
> it can take you so far, but if you need anything complicated it's an
> ActiveX script. You do not use VBScript (or a n other scripting
> language) to work with large volumes of data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing
> faster for shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could
> be useful ...
> Let me know what you think.
Exactly the opposite: DTS is fast and flexible. IMHO it's good that you
don't have to write a program or a bcp control file just to get data from
one db to another. Plus it's cross database - as long as both DB support
ODBC you can even use DTS with non MS databases.
robert|||"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:77DE764E-C242-4448-B1DB-33FBD0E6EC58@.microsoft.com...
> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so
> far,
> but if you need anything complicated it's an ActiveX script. You do not
> use
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster
> for
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien
BULK INSERT is great if you just need to import a single delimited file per
table but isn't a complete integration solution, which is what DTS aspires
to be.
To handle row-level validation and transformations involving multiple tables
or non-relational data sources you need somewhere to put the transformation
and validation logic. Broadly, the two common approaches are: 1) ELT -
Extract and Load to staging tables. Transform and validate in the database.
2) ETL - Extract, Transform and validation process first, then Load to the
database. DTS is usually positioned somewhere between these two approaches.
In my experience, when it comes to true ETL capability DTS falls short on
features and performance compared to rival integration software products.
That's a signifcant issue because the ETL appraoch has important benefits
for many enterprises. That's why DTS probably hasn't taken much market share
from the likes of Informatica and Ab Initio.
SQL Server 2005 Intergration Services is a big improvement on DTS. It
remains to be seen what market that will find now Microsoft is pushing
harder to promote SQL Server's potential as a complete BI tool suite.
David Portas
SQL Server MVP
--|||Damien wrote:
> I'm gonna say something slightly controversial just to get a bit of
> debate going, but I think DTS is just a drag and drop wizard for
> programmers who don't know what they're doing. As with any wizard,
> it can take you so far, but if you need anything complicated it's an
> ActiveX script. You do not use VBScript (or a n other scripting
> language) to work with large volumes of data.
I've only used it for small or one-off migration projects, but it's been
really nice to not have to really think about the programming and to just
drag and drop and set some parameters and go.
One of the most complicated situations I've used it in was for testing some
VB apps and stored procedures for a large data integration project. We were
getting data in Access tables from an outside source, importing them to
staging tables, manipulating them with stored procedures and populating
other tables. There were several steps and combinations of VB apps and
stored procedures because there were several types of data coming in and
going out and they had to be in specific order and with some assumptions of
values set by a previous step.
I was able to create several DTS packages to clear out and re-load the data
and initialize values to simulate a previous step in order to trouble-shoot
various stages of the process. All this without taking time to write an
extra application. I knew what I was doing obviously (I wrote the VB apps
and stored procedures), but it was really quick to use DTS in this case
because I could just point and click and run the process, then get back to
work on the real application for the client. Another benefit of DTS in this
case was that I was working remotely through a secure VNC tunnel and
recompiling the VB apps on my machine, FTPing them over and moving them to
the right directory was a pain (security was very rigid). With DTS, the
packages were stored right there in the server, so I didn't have to looking
anywhere else for them or upload them after changes were made.
There are multiple ways to solve different problems, but this one seemed to
fit the bill quite well.
David Cornelius
http://CorneliusConcepts.com|||The more complex the transformations, then the slower DTS will run. If your
transformation VBScript task is making calls to external COM objects (like
the File System Object or a custom DLL component), then performance will
seriously take a hit. It is true that seperating the load and transformation
steps can improve performance and perhaps make the transformation
programming more maintainable. Here is the method I use for loading 10 GB+
daily into a data warehouse.
#1 Bulk copy / DTS raw source data into staging database / tables.
#2 Run data quality queries against the staging tables to verify
referential integrity, etc. This is especially important if you are
importing data from a 3rd party applications, and it's best to detect and
resolve these issues before it reaches production.
#3 Select rows from staging tables into production database / tables
using T-SQL for the transformations.
#4 Drop or truncate the staging tables.
For performance reasons, the staging database can be placed on a seperate
disk drive from the production database and RAID is optional.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:77DE764E-C242-4448-B1DB-33FBD0E6EC58@.microsoft.com...
> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so
> far,
> but if you need anything complicated it's an ActiveX script. You do not
> use
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster
> for
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien|||Thanks for the input guys!
"Damien" wrote:

> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so far
,
> but if you need anything complicated it's an ActiveX script. You do not u
se
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster f
or
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien