April 27, 2005 at 4:41 pm
I have an automated DTS process that:
1. truncates a sql 2000 table, table A.
2. Uses an ODBC connection to access a minicomputer that populates tableA with new data.
One day the process ran, truncated tableA, but the mini was down so we lost our data in tableA and didn't re-populate tableA either. No good. (If we knew the mini was down, we would not have truncated tableA)
Under DTS what is the best way to handle this problem? (I was thinking of INSERTing tableA into a temporary table, truncating tableA, going to the mini for data, and hopefully getting some. In case the mini was down, at least we would have a copy of the original tableA. Better than nothing but hardly eloquent)
TIA,
Bill
April 28, 2005 at 10:17 am
A quick and dirty way would be to:
a. Create a staging table somewhere that is populated by the mini
b. Do a select from the staging table. If no data found, do whatever you need to do.
c. If data found, then truncate your destination table and insert using the staging table.
d. truncate the staging table so that it is empty for the next run.
e. back to a.
April 29, 2005 at 7:25 am
I have lots of dts packages like this, I use an 'Execute SQL Task' to do a select on the source table (using a single key) and only execute the truncate if it is successful.
If you want to fully protect against any data loss (ie lost connection during data transformation) then Jonathan's solution of using a staging table is best.
I think you can set the transaction level for DTS to make the whole package one tranaction (but you have to delete rather than truncate the table), but I am not sure.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply