Search code examples
ssisetlpentaho-data-integration

SSIS - Updating destination table with the non matching rows from origin table (Using Pentaho Data Integration)

Here is my question:

I would like to update in my destination table the rows that not match from the origin, here is an example:

TABLE A (ORIGIN)

1234 3456 4235 5664 2345 2349

TABLE B (DESTINATION)

1234 3456 4235 5664 2345

As you can see the row "2349" is it not in the destination table (TABLE B), so there is a way with ssis (i am using pentaho data integration) to 'say': hey! look the non matching rows between table A and table B and then update me the table B with that non matching rows?

Solution

  • Yes, from the description of your problem, you want to INSERT into table B the rows that not exist in table A. You can do it in one transformation:

    • With two Table input steps, you select all rows from table A in one of them, and you select all rows from table B in the other.
    • You add two Sort rows steps, each after the Table input, to sort rows by the column/s you want to compare
    • You add one Merge join step and both Sort rows steps pass their data to the Merge join, you define a left join, and join rows by the columns you want to compare, left are the rows coming from Table A, and right the rows coming from Table B, so the output are all rows from Table A adding the information of rows from Table B that meet the merge condition.
    • You add one Filter step to get only the rows in Table A without information from Table B
    • You insert those rows into Table B with the Insert table step. enter image description here