Tablediff Utility – Output File Encoding in SQL Server

Run TableDiff.exe against a view configured to limit the source table in the same way your replication filter does. I've created an example here:

USE Test;

    /* Create two test tables, TableDiff1 is the source and TableDiff2 is the destination */
CREATE TABLE TableDiff1
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff1_TableData DEFAULT (NEWID())
);

CREATE TABLE TableDiff2
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff2_TableData DEFAULT (NEWID())
);


    /* Create some dummy data in TableDiff1 */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (2, DEFAULT);


    /* Copy the dummy data into TableDiff2, with a FILTER */
INSERT INTO TableDiff2 (FilterColumn, TableData)
SELECT FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

    /* Add some more data to TableDiff1 that does not exist in TableDiff2
       so TableDiff.exe shows differences between the two tables */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);

SELECT * FROM TableDiff1;
SELECT * FROM TableDiff2;

Next, we run TableDiff.exe against both tables, clearly there will be differences since we are not excluding the filtered rows (FilterColumn = 1)

C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1 -destinationserve r localhost -destinationdatabase Test -destinationschema dbo -destinationtable T ableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1] on localhost and Table [Test].[dbo].[TableDiff2]
 on localhost have 2 differences.
Err     TableID
Src. Only       3
Src. Only       4
The requested operation took 0.1573824 seconds.

Next, we create a View that has a WHERE filter to select only the FilterColumn = 1 rows:

GO
CREATE VIEW TableDiff1_Filtered
AS
SELECT TableID, FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

Now, when we run TableDiff.exe against the view, we get the desired results. Only a single row is identified as missing from the destination table (the row we added after "replicating" the rows from the source table).

C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1_Filtered -destina tionserver localhost -destinationdatabase Test -destinationschema dbo -destinati ontable TableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1_Filtered
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1_Filtered] on localhost and Table [Test].[dbo].[Ta
bleDiff2] on localhost have 1 differences.
Err     TableID
Src. Only       4
The requested operation took 0.1220787 seconds.

In the Columns page on the OLE DB Source, you can use the check boxes beside the columns to change the order.

The columns are added to the output in the order in which they get checked, so what you need to do is uncheck all the columns, and then check them again in the correct order.

Downstream data flow tasks will want to update themselves because the column mappings "broke", but this shouldn't be a big issue to fix.