DTS global variable issues

[SqlHunter]

Iam using a DTS package:
I have 2 database and 2 tables
I have to insert data from database1 (Table1) to database2(table2)

conditions:
1. I have to get the max(emp_id) from source table(table1) and assign it to a global variable and then use this query:
select * from table1 where emp_id < (global value)
2.Now I have insert this recordset to the table2 of database2
3.Now Delete the dataset from table1 where emp_id<(global variable value)

Now I need to schedule this process since every now and then there will be entry to table1 and i have to insert the dataset till the maximum id value to the table2 and then delete those from table 1.

I used a execute sql task task for passing the global variable.

 

[secondguess]

could you do this with a few bits of SQL within your DTS (rather than the conventional transformation mechanism)?

1. insert into tableB values (Select * from tableA where empid < (select max(empID from tableA)))

2. delete from tableA where empid < (select max(empID from tableA))

or does the data change too fast? (in which case you could put a temporary table step in between and delete from tableA based on the contents of that - e.g. delete from tableA where empid <= (select max(empID from tableC)))

hth

 

[SqlHunter]

yes the data changes fast and I need to use a DTS and schedule it for a particular time interval.So I need that global variable to be passed

 

[SqlHunter]

Finally i reached to a better stage.....
1. Used select Max in the execute sql task and declared a global variable as an output parameter where it is used in the next select statement select.....where emp_id <=? (in the connection between 2 databases)and passed the global variable as an input variable now it works fine
2.Now used another execute sql task to delete whatever is transfered from the source as delete.....where emp_id <=?..Here also passed the global variable.
3.So now the sequence works perfectly.

Now I have an issue where if there is a problem with the delete statement like foriegn constraint or something like that I want to roll back the process.Is that possible and how will I capture that error..Can we use the transactions in DTS