I would like to create a SQL Server agent job that would run every 10 minutes. The job would check for new records in a table on a remote database (linked server already created), if exist the job would import the new records to destination database.
I've tried SSIS, but I can't get it to work. From SSMS on the destination, having connected to the remote SQL Server Express database and selecting Task/Export to create an SSIS package, I would supply the select statement
select top 1000 * from "servername".DBname.dbo.table order by Created desc
On finishing the SSIS job creation, the job would run immediately and update new records to the destination DB. However, when I rerun the job from SSMS, it doesn't work.
Preferably I would like to just add new records from remote DB to destination DB, not just by adding last 000 rows which may encounter duplication.
Thank you for your time