1

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
orderid
product
price
quoted price
discounted price
Server2
Database 2
Table 2
orderid
createdon
modifiedon
Table 3
orderid
createdon
modifiedon
status

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 & 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
1
  • @RDFozz actually, i somewhat know how to compare when they are in same server + database, but just o avoid the two fold process to import and process i want to use SSIS for it. I will update the question with the query i have crafted for it that makes the question just "how to use it in SSIS ?" Jul 24 '18 at 7:34
3

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 & 2) or they match exactly (else), there is no way that they can join but differ in value.

0

Best way to do this and so you can manage it of have have other tables to compare on the future or other columns to add is to use

1 - Stagin tables to get the data from table1 to server 2 next to table2

2 - then use Merge (http://sql.sh/cours/merge) to see if the ID existe or not if id existe then write into table 3 with all the infos

if you want more explination on how to do it let me know i'll do a little discription with screen shots

1
  • Hmm, but it sounds like a two fold process Jul 23 '18 at 16:54
-1

This youtube one worked for me. I had the same issue years back. Its not so easy from the online instructions books. It they all seem to assume to much. I used a Youtube tutorial. That took you through the steps.

https://www.youtube.com/watch?v=UVDBmD6Vsok

However be sure to add sorts if your not using order by.. But you can't use this to compare any new columns being added unless you compare sys tables using a specific sql query.

1
  • 3
    Can you add the steps on your answer? The link might break and the solution will be lost.
    – EzLo
    Jul 23 '18 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.