I need to translate the next SQL Query in SSIS: (Each table belong to a different source - SQL Server & ORACLE)

update A
set
A.col1 = B.col1
A.col2 = B.col1
from
table A inner join table B B on A.col3 = B.Col3
where
A.col4 = value 1 and
A.col5 = value2 and
B.col4 = value 3;

As you can see, source and destination corresponds to the same source: table A. This is the work flow I have created:

After the conditional split I have used a Derived Column in order to copy the column B.Col1 to use it on the OLE DB Command to update the columns of table A After that, I have write the next piece of query in the OLE DB Command task:

update Table A
set
col1 = ?
col2 = ?
where 
A.col4 = 5 and

A.col5 = 5 and
A.col3 = ? and  -- for the join 
? = 11;

These are the values I used to prove the query:

Clarifications: The code is just a schema, so I do not need correction about it, that is not my doubt (in case I have made a mistake).

I was asked to make this translation without modifying the sql query.

I can not create staging areas.

If you need to know more to give help, please ask, but be polite.

Regards

Instead of using the UNION ALL, I would use a MERGE JOIN to do the inner join logic.

Just a remark: the OLE DB command will update the table row by row, and is thus incredibly slow for large data sets.

Hi dj2907,

According to your description, I create a sample package in my environment. Based on my test, I can upgrade the table A correctly.

The following screenshots are for your references:

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong