Very Computer

I have just started investigating DTS and my source thus far is Wrox's
"Professional SQL Server 2000 DTS". The book leans heavily on the use of
ActiveX scripts and the multi phase data pump. With an approach like this it
seems that the DTS package would attempt to insert only one row at a time;
this would seem to be a very slow approach.

I have been giving thought to the idea of blending the two approaches by
utilizing much of the code in the existing load procedures but roll it all
into a DTS package. I realize that I haven't given much to go on, making
these rather loaded questions, but...

Is there any real advantage to migrating to DTS? The existing load
procedures perform most of the "transformations" in bulk insert/update
statements.

Where might I find some other real world approaches that don't rely so
heavily on ActiveX scripting?

Are my thoughts even correct, is the ActiveX approach considered to be a
sluggish approach?

I would appreciate any insight regarding common practices that utilize DTS.

Thank you very much,
Ken

Yes, you can end up doing row-by-row inserts and updates using some of DTS's
features (you can always use SQL Profiler on the destination server to see
exactly how a particular approach works), and this can be very slow relative
to bulk and set-based operations.

I've used DTS to control bulk insert and batch SQL operations (as in your
"blending" approach") with good results in many ETL processes.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

In 90% of the cases the SET based approach is best.  Yes DTS can get
down to row by row but it can also perform SET based stuff too.  I
always without exception BULK INSERT a text file into a SQl Server
table.  I then decide on what I need to grab from elsewhere and the
complexity of the logic.  

If I can do it using SET stuff I do (generally in An ExecuteSQL task)
and the bits I can't I use DTS and an ActiveX transformation

My approach is use the tools you have available to their best
potential.

Allan
www.SQLDTS.com

On Mon, 12 Aug 2002 21:28:58 -0500, "Ken Sturgeon"

Thank you both for your input. Clearly I have alot of learning curve ahead
and it will take some time to convert our approach.

Thanks again,

Ken

Quote:> I am involved in a process that includes nightly batch feeds of 40 flat
> files from an AS400 system. I've inherited a rather complex but impressive
> set of stored procedures that manage the load process by first performing
a
> bulk copy of the feed files into corresponding feed tables; subsequent
> procedures process the data into production tables. The entire process
> includes data validation, error reporting, translations, look ups and
> replication. Natural keys are concatenated in the feed files to establish
> "original key" values that are used to populate FK references to identity
> fields in parent tables.

> I have just started investigating DTS and my source thus far is Wrox's
> "Professional SQL Server 2000 DTS". The book leans heavily on the use of
> ActiveX scripts and the multi phase data pump. With an approach like this
it
> seems that the DTS package would attempt to insert only one row at a time;
> this would seem to be a very slow approach.

> I have been giving thought to the idea of blending the two approaches by
> utilizing much of the code in the existing load procedures but roll it all
> into a DTS package. I realize that I haven't given much to go on, making
> these rather loaded questions, but...

> Is there any real advantage to migrating to DTS? The existing load
> procedures perform most of the "transformations" in bulk insert/update
> statements.

> Where might I find some other real world approaches that don't rely so
> heavily on ActiveX scripting?

> Are my thoughts even correct, is the ActiveX approach considered to be a
> sluggish approach?

> I would appreciate any insight regarding common practices that utilize
DTS.

> Thank you very much,
> Ken