Can someone guide me how to compare tables with SSIS I have two tables in two different databases in two different servers. 

Both tables have an orderid column, and a given value for orderid references the same entity in both tables.

I want to check whether each value for orderid in either table has a matching entry in the other table, and write a record to table3 based on this information. table3 has the same basic columns as table2, with the additon of a status column.

Server1
Database1
Table 1
orderidproductpricequoted pricediscounted price



Server2
Database 2
Table 2
orderidcreatedonmodifiedon

Table 3
orderidcreatedonmodifiedonstatus




The status can be matching,not matching,only in table 1,only in table 2,error. If in both orderid is same the status will be matching, if it's there only in table 1 it becomes only in table 1, if found only in table 
 2 it's only in table 2 not sure whether not matching &amp; error are relevant in this scenario.  

I have crafted an sql for it, just let me know how to use it in SSIS

insert into Table3 (orderid,createdon,modifiedon,status)
select
    COALESCE(t1.orderid,t2.orderid) as orderid,
    COALESCE(t1.CREATE_DATE,t2.CREATE_DATE) as createdon,
    COALESCE(t1.UPDATE_DATE,t2.UPDATE_DATE) as modifiedon,
    case when t1.orderid is null then 'only in table 2'
         when t2.orderid is null then 'only in table 1'
         when t1.orderid = t2.orderid then 'matching'
         when t1.orderid != t2.orderid then 'not matching'
         else 'error'
    from Table1 t1
    full outer join Table2 t2
    on t1.orderid = t2.orderid
If you really want to do this in SSIS, add 2 sources with the SQL from each table and link them to a Merge transformation, which will do a FULL JOIN (select it from the drop down) operation on your columns orderid between the 2 data sets. SSIS needs both data sources to be ordered by the column you are joining so either add an ORDER BY orderID on both your sources and mark the column orderID as SORT on the advanced editor (so the merge knows that data is sorted by that column), or add a custom Sort transformation between the source and the merge on each flow to explicitly order by this column.

Then you can add a Derived Column transformation right after the merge with an expression to determine if either:


OrderID1 is NULL (record is from data set 2).
OrderID2 is NULL (record is from data set 1).
OrderID exists on both data sets.


If you also need to know if data changed when both order ID's match, then you will have to write an awful expression comparing each column against each other, making sure to consider NULL values. SSIS isn't good to build expressions (they are hard to read and even harder to maintain), that's why I always recommend to do this directly on SQL.

Finally link the derived column to your destination database.



It's worth mentioning that from your FULL JOIN example, the following CASE scenario:

case 
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    when t1.orderid = t2.orderid then 'matching'
    when t1.orderid != t2.orderid then 'not matching'
    else 'error'
end


Can be simplified into:

case 
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    else 'matching'
end


Since you are joining by orderid, either they don't join on each side (option 1 &amp; 2) or they match exactly (else), there is no way that they can join but differ in value.