Need Opinions on DataSet usage

[-=JLK=-]

All,

I'm looking for opinions on the best way to accomplish a given task.
Here is the senario:

I have a Data Warehouse that I need to update with information that has been
loaded into a staging database. We are dealing only with three tables (a
source table, and the destination dimension and fact). I need to process
each "new" record in the source (staging) table, transform the data and then
update or insert into the dimension table and insert into the fact table.

Question:
Is it better to simply use a data reader to interate through each new record
and then on an individual basis using a command object simply update or
insert into the dimension/fact table as appropriate? Or is there a reason
to use the DataSet for all operations and if so what would be the strategy
for loading the Dataset table for the Dimension with both the rows to be
added and the rows to be updated? I don't want to go out and retrieve ALL
the dimension records which could be 100's of thousands of rows just to
locate the ones that need to be updated. Also I can't identify the ones to
be updated until some of the transformation process on the source data has
occurred.

Any thoughts suggestions appreciated,

JamesK.

[Telmo Sampaio]

JLK,

Any reason for not using DTS?

Telmo Sampaio

"-=JLK=-" <No***********@nospam.com> wrote in message
news:Ox*************@TK2MSFTNGP09.phx.gbl...

All,

I'm looking for opinions on the best way to accomplish a given task.
Here is the senario:

I have a Data Warehouse that I need to update with information that has been loaded into a staging database. We are dealing only with three tables (a
source table, and the destination dimension and fact). I need to process
each "new" record in the source (staging) table, transform the data and then update or insert into the dimension table and insert into the fact table.

Question:
Is it better to simply use a data reader to interate through each new record and then on an individual basis using a command object simply update or
insert into the dimension/fact table as appropriate? Or is there a reason
to use the DataSet for all operations and if so what would be the strategy
for loading the Dataset table for the Dimension with both the rows to be
added and the rows to be updated? I don't want to go out and retrieve ALL the dimension records which could be 100's of thousands of rows just to
locate the ones that need to be updated. Also I can't identify the ones to be updated until some of the transformation process on the source data has
occurred.

Any thoughts suggestions appreciated,

JamesK.

[-=JLK=-]

Actually we do use DTS, though we have found it limiting in places. Using
VB.net is simply an alternative we are exploring for some situations. That
is what lead to my current question of which would be better the DataSet or
DataReader based on the given scenario. As may have been noticed in a
different post I made I was looking at the ability to "add" a row to the
dataset but fool it into thinking it was an update not an insert when the
key was already present in the target table, which would then preclude
having to retrieve the whole table to begin with.

Thanks,

James K.

"Telmo Sampaio" <te***********@hotmail.com> wrote in message
news:uV**************@tk2msftngp13.phx.gbl...

JLK,

Any reason for not using DTS?

Telmo Sampaio

"-=JLK=-" <No***********@nospam.com> wrote in message
news:Ox*************@TK2MSFTNGP09.phx.gbl...

All,

I'm looking for opinions on the best way to accomplish a given task.
Here is the senario:

I have a Data Warehouse that I need to update with information that has

been

loaded into a staging database. We are dealing only with three tables (a source table, and the destination dimension and fact). I need to process each "new" record in the source (staging) table, transform the data and

then

update or insert into the dimension table and insert into the fact table.
Question:
Is it better to simply use a data reader to interate through each new

record

and then on an individual basis using a command object simply update or
insert into the dimension/fact table as appropriate? Or is there a reason to use the DataSet for all operations and if so what would be the strategy for loading the Dataset table for the Dimension with both the rows to be
added and the rows to be updated? I don't want to go out and retrieve

ALL

the dimension records which could be 100's of thousands of rows just to
locate the ones that need to be updated. Also I can't identify the ones

to

be updated until some of the transformation process on the source data has occurred.

Any thoughts suggestions appreciated,

JamesK.