troubleshooting Question

Excluding hits from one table when selecting in another using SSIS

Avatar of denpet
denpet asked on
Microsoft SQL Server
14 Comments1 Solution348 ViewsLast Modified:
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
E.g.
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.
ASKER CERTIFIED SOLUTION
Reza Rad
Consultant, Trainer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros