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

Question

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.

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.

Solution 1

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'

Solution 2

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.

Solution 3

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 paste the below sql code
3. add oledb destination and map the columns with table3

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'

I think this will work for you.

Comments

[Nick.McDermaid]

You don't even need SSIS for this. You can crate a SQL Agent Job and create a step of type Transact-SQL Script (T-SQL)

[Frank Martin]

Thanks for the tip. Very helpful as I didn't know about this. But in my case I am also doing some other operations before I run this query so I have to use SSIS.

[Nick.McDermaid]

Without extending this discussion too far.... you can probably also do those in a SQL Agent Job. But whatever you're most comfortable with. SSIS certainly looks better.