sdevanny (7/10/2013)
Forgive me if I have oversimplified or misunderstood the issue, but this is how I understand it:From 10,000 feet as I understand it, your existing package(s) restores the backup (test_stage) and "copies" (inserts?) the data from test_stage into test_cdc. I would suggest that you add Lookups to check if the data you are processing from test_stage exists in test_cdc before inserting. Then you can direct the matching rows to be processed one way (i.e. update/ignore - whatever your requirements dictate) and direct the non-matching rows (i.e. new records) to be inserted into test_cdc. Again - this is a 10,000 foot solution and if you believe it might satisfy your issue I'll be happy to get into the details with you.
Thanks for everyone that has got back to me.
The above scenario is basically what I want. It would appear that the business does not need the level of reporting that CDC gives you so all I want to do is transfer the "new" records from test_stage to test_cdc.
I have now created a simple package.
It has 1 Data Flow Task.
Within that Dataflow task I have -
1 Source Assistant
1 Lookup
1 Destination Source
The Source Assistant is connected to test_stage and looks at the CLIENT table.
Two columns in this database - client_id and client_name
This attaches to Lookup.
Full Cache code, OLE DB connection manager and Redirect rows to no match output,
On connection manager this looks at test_stage and the CLIENT table.
Columns the client_id from Available Input Columns is connected to the client_id in Available Lookup Columns
This is then connected to the Destination Source, which uses a connection manager to test_cdc abd a table called CLIENT
On mappings client_id and client_name are mapped to the coprresponding table.
I run the package and all 12 entries from client_ref in test_stage go to test_cdc. Brilliant.
I run it again and 12 entries copy again. This is what I am trying to avoid. How with the lookup do I just copy what doesn't exsist in the destination table?
Thanks