I have 2 tables. At a timed interval I have "transform Data task"
setup to copy the data from one table to the other. I want to delete
the transfered data from the first one once the transfer is complete.
I can create the SQL task to do the delate. However, I was think, what
if there is data inserted into the first table will the tranfer is
going on and before the Delete process runs? Is there a way to insure
I delete only the data that was transfered and any new data will
remain until the next cycle?

Bart

Move the load data to one side whilst you do it and allow your other processes to continue filling the original table

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


<Bart> wrote

yeah you can do a table lock

if i were you, i would give a LOT more detail because i'll bet there is an easier way to do this

--
Message posted via http://www.sqlmonster.com

Additional background:

I manage a SQL server that gets updated from multiple manufacturing
equipment, 24 /7. Additionally, we have a IBM AS400 mainframe that
needs some of the manufacturing information. Currently, each piece of
production equipments updates the AS400 and the SQL server. To
eliminate all that traffic, I will have the production equipment write
to a table on the sql server and then on a periodic basis, I need to
move the "start" and "stop" transactions to the AS400, deleting from
the SQL as they are uploaded. Although the transfer time will be short
(around 4 to 5 records per upload) and there probably will not be any
updates to the SQL table between the time I select the records to
upload and when the upload is finished,I can't be positive I didn't
delete a record that has not been transfered.
It would nice if I could select the records, update a field (like a
"uploaded" bit field), do the transfer and then do another select to
get all the records that had the field flagged. I can do that with a
external program but I thought that it would be possible in DTS. I
suspect I could use an ActiveX stript task to do that. I was thinking
that maybe someone else had run into a similar situation.

Bart

On Tue, 25 Jan 2005 21:53:58 GMT, "aaron kempf via SQLMonster.com"
<forum (AT) SQLMonster (DOT) com> wrote: