How to compare two tables in SSIS? (SQL Server)

I am creating an SSIS package that will compare two tables and then insert data in another table.

Which tool shall I use for that? I tried to use “Conditional Split” but it looks like it only takes one table as input and not two.

  • Pass string into SQL WHERE IN
  • Php SQL Server table value parameter
  • Getting latest rowversion/timestamp value in update statement - Sql Server
  • Is this good writen transaction in stored procedure
  • CDC is enabled, but cdc.dbo<table-name>_CT table is not being populated
  • CTE SQL query get full paths
  • These are my tables:

    TABLE1

    ID
    Status

    TABLE2

    ID
    Status

    TABLE3

    ID
    STatus

    I want to compare STATUS field in both tables. If Status in TABLE1 is “Pending” and in TABLE2 is “Open” then insert this record in TABLE3.

  • Changing SqlConnection timeout
  • Database Designing: An art or headache (Managing relationships)
  • MsSQL callback function to asp.net website
  • How can I determine installed SQL Server instances and their versions?
  • SQL Server Join In Order
  • SELECT MAX returns the wrong value
  • 3 Solutions collect form web for “How to compare two tables in SSIS? (SQL Server)”

    If your tables are not large you can use a Lookup transformation with Full Cache, but I wouldn’t recommend it because if your tables grow you will run into problems. I know I did.

    I would recommend Merge Join transformation. Your setup will include following:

    • two data sources, one table each
    • two Sort transformations, as Merge Join transformation needs sorted input; I guess you need to match records using ID, so this would be a sort criteria
    • one Merge Join transformation to connect both (left and right) data flows
    • one Conditional Split transformation to detect if there are correct statuses in your tables
    • any additionally needed transformation (e.g. Derived Column to introduce data you have to insert to your destination table)
    • one data destination to insert into destination table

    This should help, as the article explains the almost exact problem/solution.

    I managed to do it by using “Execute SQL Task” tool and writing the following query in it.

    INSERT INTO TABLE3 (ID, Status) SELECT * FROM TABLE1 t1, TABLE2 t2 WHERE t1.ID = t2.ID and t1.status = 'Pending' and t2.status = 'Open'
    

    i think so this is what you are looking for.?

    In your case if both the tables are Sql tables then follow the steps below

    1. Drag dataflow task
    2. Edit dataflow task add Oledb source and
      in sql command past the below sql code

      select b.id,b.status
      from table1 a
      join table2 b on a.id = b.id
      where a.status = ‘Pending’ and b.status = ‘open’

    3. add oledb destination and map the columns with table3

    I think this will work for you.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.