How to create an SSIS package to compare SQL server Database Objects in different servers and generate the difference in an Excel Sheet. Is it possible to do that?
Asked
Active
Viewed 1,225 times
-1
-
I don't see why not. – Tab Alleman Dec 08 '14 at 14:32
-
Can you please give me some ideas to implement the logic.. – sarthak dash Dec 22 '14 at 14:52
-
The only thing I can think of is a script task. You can query the information schema tables of both databases, and every time you find a difference, write it to an excel file. – Tab Alleman Dec 22 '14 at 15:50
1 Answers
0
SQL Server Data Tools (SSDT) for Visual Studio 2012
SQL Menu > Schema Compare > New Schema Comparison...
Allows you to point to two different databases, then...
- The Options button (looks like a gear) allows you to set filters on what is being compared, i.e. tables, views, SP's, users, etc.
- Hit the Compare button to compare the two db's, and list all differences
- Allows you to check / uncheck all differences to be updated or not, and
- The Update button executes the above changes.
This isn't an SSIS package, and can't dump differences into Excel (afaik), but it can generate SQL Script of all changes.
Jim Horn
- 879
- 6
- 14
-
Hi Jim, Thanks for the response but My requirements are different. I am looking for a table(With different table schemas) in two different databases with different names.So basically i need to find a table(eg: dbo.tableA) from database X and then look for it in the other database Y for any difference in the table(abc.tableA) structure. The table may or maynotexist in both the databases. I am looking for an automated approach for this which can be done by SSIS. – sarthak dash Dec 15 '14 at 15:22