I have the following problem.
I need to transfer data from a table in a source database, to a destination table in another database.
But, I only want to include new values in the source database, and not those already in destination.
Kind of like
select * from source where id not in (select id from dest)
But, it's two different datasources so this will not work.
Simply selecting all and letting the insert fail is not an option, as the dataset is quite big, and it also includes a sub table (e.g. order - order_rows, so I only want to process new orders, or order_rows will be filled up with duplicates, as there is no common key between the source and dest on order_rows level).
I think the solution is to select orderno into a ADO RecordSet from destination, and then use this in the query on source.
I have managed to create the recordset, but can't find a way to use it in the source query
First, on dest: select orderno from orders -> result into variable excludelist
Then on source: select * from orders where orderno not in (select orderno from <excludelist>)
where <excludelist> is just a place holder for what really goes there to use it in the query.
An alternative would be to create a temp table on source and then transfer the order numbers from destination to it, and use this in the source query. But, at present the SSIS user is only data reader in source, and I like to keep it that way if possible.