Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server by serverku

The last time we saw tablediff Utility basis and tablediff utility for multiple tables using SSIS. As you know using sssis package it generated discrepancies log files for database changes which we have to apply on destination servers\databases to make them seem. Now this post is extended for the same in which we will take care to automatically apply the discrepancies for each file generated at destination.
I am writing here for the next portion of previous post using ssis package for multiple tables and changes to be generated. Let create remaining part and add to existing package.

In the previous post we have visited first three steps and here I added last three steps (4 to 6). For the steps (1 to 3) please visit this post. Let’s continue with the remaining steps,

Step 4 : For each loop container
Retrieving each log files for the process,

Here we have taken one more variable to capture full log file path in variable named “Filename” and the following process will do the same,

Step 5 : Execute SQL Task
Taking destination database connection and using file connection,

To apply multiple file changes to destination, taken expression for FileConnection,

Step 6 : File System Task
Moving files to other location after process, so does not repeat next time,

After completing and running all the steps we will have the destination database to same as source , let us run first three steps (1 to 3) after changes apply and verify if found any other discrepancies,

You can see after this run, we have a message by tablediff utility is “Source table and destination table are identical” and it won't generate any discrepancies log files more. I want to know you are using, these steps to apply changes to destination using ssis? Waiting for your comments!

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.

SQL Server tablediff Utility by serverku

Earlier, when I was working with task to sync data for two tables between two databases, I got the chance to use the tablediff.exe utility provided by SQL server. It used to compare data for two tables which have similar columns and data type structure. After comparing it generates transact SQL script log for discrepancies.

We can use with command line or with a batch file. Let us see how can we use with command line. This utility is found in “C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe” path or wherever the SQL server installed. tablediff.exe used to compare table data in same servers\databases or different servers\databases. The syntax to use it as following.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
    –sourceserver [SourceServer]
    -sourcedatabase [SourceDatabase]
    -sourceschema [SourceSchema]
    -sourcetable [SourceTable]
    -sourceuser [SourceUser]
    -sourcepassword [SourcePassword]
    -destinationserver [DestinationServer]
    -destinationdatabase [DestinationDatabase]
    -destinationschema [DestinationSchema]
    -destinationtable [DestinationTable]
    -destinationuser [DestinationUser]
    -destinationpassword [DestinationPassword]
    -et Difference
    -f [FullFilePath]

You can find other arguments and more specification here.   Let's go through with small testing, creating Domo objects and use in the example.

USE SourceDB
GO

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

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

USE DestDB
GO

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

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

SELECT * 
FROM Sourcedb.dbo.SourceObj
SELECT * 
FROM Destdb.dbo.DestObj
GO

Now, turn on tablediff.exe and batch created with the following code.

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
-sourceserver [DemoServer] 
-sourcedatabase [SourceDB] 
-sourceschema [dbo] 
-sourcetable [SourceObj] 
-sourceuser [sa] 
-sourcepassword [test@1234] 
-destinationserver [DemoServer] 
-destinationdatabase [DestDB]
-destinationschema [dbo]  
-destinationtable [DestObj] 
-destinationuser [sa] 
-destinationpassword [test@1234] 
-et Difference 
-f C:\DiffOutput

After creating a batch file with the above code and run it and the output resulted named “DiffOutput.sql” in C: drive which is in the form of the SQL which can be executed in SQL server. Following are the changes as described in the above image.

-- Host: DemoServer 
-- Database: [DestDB] 
-- Table: [dbo].[DestObj] 
UPDATE [dbo].[DestObj] SET [CreatedDate]='2012-09-29 04:11:34.820' WHERE [id] = 1 
INSERT INTO [dbo].[DestObj] ([CreatedDate],[id],[name]) VALUES ('2012-09-29 04:11:34.820',2,'test2') 
DELETE FROM [dbo].[DestObj] WHERE [id] = 3

You can see the changes generated, we have to apply to destination databases, so data will properly sync from source to destination. I will post the next topic to use tablediff.exe in size, and it will be with multiple tables. Please share your thought here to use in any other way.