Is DTS the best approach for my needs?

Is DTS the best approach for my needs?

Post by Ken Sturgeo » Wed, 14 Aug 2002 11:28:58



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

 
 
 

Is DTS the best approach for my needs?

Post by Bob Pfeiff [MS » Wed, 14 Aug 2002 21:41:09


Generally in an extraction, transformation, and loading (ETL) process of
large amounts of data, bulk and set-based operations like the ones you
describe are a good approach.

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.

 
 
 

Is DTS the best approach for my needs?

Post by Allan Mitche » Thu, 15 Aug 2002 04:02:30


Well this is a good question

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"


>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

Allan Mitchell
www.SQLDTS.com
 
 
 

Is DTS the best approach for my needs?

Post by Ken Sturgeo » Thu, 15 Aug 2002 22:08:16


Bob and Alan,

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

 
 
 

1. DTS process needs to be able to run while I am not logged into ODBC Datasource

How would one automate or store the user information required to log onto an
AS/400 Client Access ODBC driver without having to be logged on to the NT
system?  I have read articles from IBM's website on setting up the ODBC
Client Access driver as a service, but would like to explore other solutions
and decide on the proper one.  Thank you.

2. US-NJ-ORACLE HRMS 11I OAB FAST FORMULA

3. APPROACH APPROACH APPROACH

4. Command Center - Reports

5. Date Filtering question - what is the best approach

6. Howto manually installing ASA 7.0?

7. What's my best approach?

8. Startup time for INF-ONLINE 7.31.UC2

9. Security - best approach?

10. Help: Best Approach to New User of RDO

11. Best approach to upgrade SQLServer 7 to SQLServer 2000

12. Best Approach

13. Best approach for fast record access???