K
Kim
I'd appreciate some advice on the following scenario
What I'd like to know is which is the best approach to achieve the task in
an efficient and good manner.
It's an integration between two systems.
I get data from one of them once a week in a text file that I'll get from a
network share, it's tab delimited.
Depending on the name in one of the columns (textfile) I'll have to look for
the existence of that name in one sql server 2000 table and get the id,
with the id (pk) I'll be updating other tables with values from the
textfile, if the id (that is the name) does not exist I'll be inserting it
to the main table and to other tables if certain requirements are
fullfilled, under certain circumstances I won't insert it but log the record
with a message telling why it wasn't inserted to a log file. The textfile
will contain about 1000-2000 records.
How should I tackle this to gain performance, should I use sql-server DTS to
accomplish everything, or perhaps go with ado.net and if so, which ado.net
objects are suitable for this task, should I use the dataadapter and
commandobject ? should I use it with stored procedures and what are the pros
and cons and the best way to achieve this ? looping through all records and
then calling a proc for each record ?
now when it comes to a critical scenario I'm in doubt regarding the best way
to do this, thinking that books and samples I've read have been showing
samples about how to do similar things but saying nothing about which way
might be the best and for what reasons.
youre opinions appreciated
Kim
What I'd like to know is which is the best approach to achieve the task in
an efficient and good manner.
It's an integration between two systems.
I get data from one of them once a week in a text file that I'll get from a
network share, it's tab delimited.
Depending on the name in one of the columns (textfile) I'll have to look for
the existence of that name in one sql server 2000 table and get the id,
with the id (pk) I'll be updating other tables with values from the
textfile, if the id (that is the name) does not exist I'll be inserting it
to the main table and to other tables if certain requirements are
fullfilled, under certain circumstances I won't insert it but log the record
with a message telling why it wasn't inserted to a log file. The textfile
will contain about 1000-2000 records.
How should I tackle this to gain performance, should I use sql-server DTS to
accomplish everything, or perhaps go with ado.net and if so, which ado.net
objects are suitable for this task, should I use the dataadapter and
commandobject ? should I use it with stored procedures and what are the pros
and cons and the best way to achieve this ? looping through all records and
then calling a proc for each record ?
now when it comes to a critical scenario I'm in doubt regarding the best way
to do this, thinking that books and samples I've read have been showing
samples about how to do similar things but saying nothing about which way
might be the best and for what reasons.
youre opinions appreciated
Kim