Oops - I didn't notice the forum - I should have guessed.
Are the databases on different servers? If not, SSIS is overkill here - why not just create a stored proc which does the transfer and schedule that in SQL Agent?
If they are on different servers and you don't want to use a linked-server approach, the SSIS process is similar:
1. Set up a MaxId variable with package scope.
2. Use an ExecuteSQL task to populate the variable with the max ID from the target table.
3. Create a dataflow which selects the rows from the source table using the maxid as a parameter to limit what is selected and map it to the target table.
Job done.
Thanks Phil.
Yes.. Databases are on different servers and don't want to use a linked-server approach
If possible, could you please provide me the screen shot how to implement your steps?
Regards
SqlStud