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