Hi, I am new to news groups and was told I might get an answer to my
question here...

I have a process that gathers information from our mainframe and then sends
this information via FTP to a SQL server in a CSV file.

I then have several DTS packages that load the CSV files and update my SQL
2000 server using DTS...

My question is - how would I best setup this process so I can update my SQL
tables using DTS while my users are currently logged into the application
that accesses the SQL server?

Right now my processes run within 5 minutes - but during that 5 minute
period I could have several to several hundred users logging in to the
application that does reads on the tables I am update with DTS. In some very
rare cases during that 5 minutes it's possible that a user could Update a
record will DTS is also trying to update a record (or delete).

Again, what is the best approach to eliminate failures - either from a user
perspective or the DTS packages themselves?

If anyone can lead me in the right direction I would really appriciate it.

Thanks....

Usually if you have users accessing the same rows that you need to update
from an import it is best to do the updates in small batches. This will
enable you to lock only a small subset of the rows at any one time and makes
for a much friendlier multi-user app. You can DTS the rows into a staging
table first and then loop thru doing the updates / deletes etc a little at a
time.

--

Andrew J. Kelly
SQL Server MVP


"Dave H" <daveh (AT) psu (DOT) edu> wrote

Thanks Andrew...

Are there any settings in DTS I should look out for? I do have it setup so
if any one step fails it won't commit any of the updates... But are there
other settings or best practices I should take care of?

Thanks again,
Dave



"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Sorry for the late reply as I have been away for the last week. The only
way to do the updates in batches is to do them in tsql and not as part of
the import. You would import all the rows into a staging table first (using
DTS, BCP etc) and then loop thru with standard tsql to do the final inserts,
updates or deletes on the production tables. You can do this as a DTS task
or outside the DTS package.

--

Andrew J. Kelly
SQL Server MVP


"Dave H" <daveh (AT) psu (DOT) edu> wrote