SQL Server tablediff utility for multiple tables using SSIS by serverku

As we saw the last post for the basic concept of tablediff utility. We learned one example, using static table, now I would like to go it with SISS package and also using more tables comparison. So, lets start it with some demo objects and created as follows. Here I am creating two tables with different databases and same SQL server instance.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.SourceObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

INSERT dbo.SourceObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 2,'test4'
GO


USE DestDB
GO

CREATE TABLE dbo.DestObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.DestObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

INSERT dbo.DestObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 3,'test4'
GO


SELECT 
 'Sourcedb.dbo.SourceObj1' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj1
SELECT 
 'Destdb.dbo.DestObj1' as Objectname,
 * 
FROM Destdb.dbo.DestObj1
SELECT 
 'Sourcedb.dbo.SourceObj2' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj2
SELECT 
 'Destdb.dbo.DestObj2' as ObjectName,
 * 
FROM Destdb.dbo.DestObj2
GO

Let see the same data inserted,

Now I will create a ssis package with passing dynamic table name and other require details. But before that I need to populate one table with same details which we need require as argument to be passed in a batch file,

USE Maintenance
GO

CREATE TABLE DatasyncDetails
(
 Id int identity(1,1),
 SourceDatabase varchar(50),
 DestDatabase varchar(50),
 SourceSchema varchar(20),
 DestSchema varchar(50),
 SourceTable varchar(50),
 DestTable varchar(20),
)


INSERT INTO DatasyncDetails
(
SourceDatabase, 
DestDatabase, 
SourceSchema, 
DestSchema, 
SourceTable, 
DestTable
)

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj1',
 'DestObj1'
 
UNION ALL

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj2',
 'DestObj2'

SELECT 
 SourceDatabase, 
 SourceSchema, 
 SourceTable, 
 DestDatabase, 
 DestSchema, 
 DestTable
FROM Maintenance.dbo.DatasyncDetails
GO

Let's check the data populated which we have to process for discrepancies,

All required demo objects created to prepare for ssis package,

and the variables used ,

I have presented two whole snaps with details of whole flow, Now we will the all the steps one by one.

Step 1 : Execute SQL Task

Step 2 : For Each Loop Container

Step 3 :  Execute Process Task

Here we will create a batch file and call this task where arguments pass from parameter we mapped in an earlier task. You can see the content of batch file and the argument used inside,

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
–sourceserver [PARESH\MSSQLSERVER2012]
-sourcedatabase [%1]
-sourceschema [%2]
-sourcetable [%3]
-sourceuser [dba]
-sourcepassword [dba@1234]
-destinationserver [PARESH\MSSQLSERVER2012]
-destinationdatabase [%4]
-destinationschema [%5]
-destinationtable [%6]
-destinationuser [dba]
-destinationpassword [dba@1234]
-et Difference
-f C:\DiffOutput\%7

Now I will use this batch file in execute process task and the use the arguments passed by For Each Loop container.

Expression of Arguments :

@[User::SourceDB]  +" "+  @[User::SourceSchema] + " " +  @[User::SourceTable] +" "+  @[User::DestDB]  +" "+  @[User::DestSchema] + " " +  @[User::DestTable] +" "+ @[User::SourceTable]

Finally done with all the steps and will have to run the package and will review the resulted SQL script log for discrepancies. So let's run it and review the out files.
Running…

Output files,

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj1]
UPDATE [DBO].[DestObj1] SET [CreatedDate]='2012-10-06 11:39:10.620' WHERE [id] = 1
INSERT INTO [DBO].[DestObj1] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.620',2,'test2')
DELETE FROM [DBO].[DestObj1] WHERE [id] = 3

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj2]
UPDATE [DBO].[DestObj2] SET [CreatedDate]='2012-10-06 11:39:10.627' WHERE [id] = 1
INSERT INTO [DBO].[DestObj2] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.627',2,'test4')
DELETE FROM [DBO].[DestObj2] WHERE [id] = 3

We can use more tables to find differences between them as we did. Here you can use server name, username & password as an argument and make it fully dynamic, but if those SQL servers can be connect from there. In the next post I will add some additional task to apply differences automatically at destination servers\databases. Hope you will like and share it.