Daniel Bowlin (12/24/2012)
This is pretty straight forward in SSISIn a dataflow,
-create a source from your prod server.
-Lookup records on your dev server. In SQL Server 2005 I believe you have to set it to ignore the failure
-Create a conditional split that compares the value in your source (prod) to the value you target (dev)
-In the split where they match send the data to a staging table
-In the split where they don't match send the data to be inserted to your target.
After the data flow runs, build an Execute SQL Task that takes the matched data from your staging table and updated the target (dev) data.
A couple of revisions
In the no match, that actually would get sent to an update process, not insert
There should be a null in some of your lookup values, the nulls would be the new records that should be inserted.