October 26, 2014 at 5:58 pm
Hello,
I'm just getting into SSIS, and need to do something which I am sure woud be easy for you lot, really I just need to know if I'm using the right tasks or not.
All I'm looking to do is find the differences in two databases, what does not appear in DB-A compared to DB-B goes to the side, and what does not appear in DB-B compared to DB-A goes to the side, both get merged, then another step I'll mention in a minute.
I was planning on using lookup tasks to find the differences in A compared to B, and visa versa and have the no match output go to a file/table (what would be best?) via a merge task, so the results end up in one place.
The extra step I think is a bit tricky, I need to then create a delete and insert statement based on the results of the merge rather than do anything with the actual data, and ultimately that is what I need, thats the goal, the delete and insert statement.
I'm not sure if, or how, I can do the last bit, and just need to know if I'm choosing the right tasks for everything that precedes it. All the sources are SQL 2008 databases.
Is what I have planned to use ok, and how can I produce the delete and insert statements from the merge results?
Thank you for any help, I'm sure you guys know your stuff!
Regards,
D.
October 27, 2014 at 2:47 am
Have a look at the TableDiff utility which comes with SQL Server - C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe is where mine is located.
You'll probably have to Google for the instructions but you could run it from an Execute Process Task in SSIS.
Otherwise you could spend lots of money on a tool to do the job.
Your deletes and inserts is really just a conditional split of your data after a full outer join of source data to destination data (key fields only will do) - if destination fields are null insert, if source fields are null delete, if in both, then update.
October 27, 2014 at 12:44 pm
Not to sound like an advertisement for redgate...(I don't work for this site or redgate)
But Redgate has a great tool in SQL Compare which would be perfect for what you want to accomplish...
October 27, 2014 at 4:58 pm
Hello,
Yeah Red Gate have great tools, but unfortunatly I'm not in a position to buy those kind of things. Thanks PJones I'll look into the table diff and see if I can get it to work for me. On the last part of your response, will that manifest itself as SQL code?
Thanks for the help.
Regards,
D.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply