For a new client we need to automate a process where they send us a new version of their database, which we then compare to the previous version to determine what was changed, and then have the changes processed.
From what I got from the help file I think Sql Data Compare might do the trick, but to ensure we're not ending up with a license for something we can't use after all, I was wondering if you could verify Sql Data Compare can indeed fulfill all of these requirements:
-- Compare a subset of tables from two data sources (at least Sql Server, but support for other DBMS's or even certain file formats would be a plus)
-- Configure which columns should or shouldn't be included in the comparison
-- Export the records that are different to some structured format that can be processed by other applications (I read that CSV exports are supported for one)
-- Configure the export to include or not include certain columns or even additional data from different tables
-- This should all be possible to drive through code (.NET / BizTalk) (preferably by accessing methods in a dll rather than having to start a command line process); the idea is to fully automate this process, so no GUI usage would be possible except maybe for some incidental configuration of the "comparison job" or something like that. In fact, I guess it would actually be a plus if you can set up the process through the GUI, then activate it through code
Questions I would have:
-- Can the comparison be done on two Sql Server database files, or does it have to be running Sql Server instances?
-- When comparing multiple tables, would this result in multiple exported CSV files, or a single one that holds the changes for multiple tables?
-- Are there any other export options?
-- We have Sql Compare licenses, and I actually have a Sql Data Compare trial on my dev machine (version 9 though). Would that be enough to try all this, or do I need additional dev kits or something like that?
My apologies for the long first post
. I'm looking forward to your replies.