SSIS - Updating a table with a OLE DB Command

问题:

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 = 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 = ?

But at this point I have a question, Am I updating only the values of the subset that I got from the conditional split, or am I updating all table A. Besides, the last part of the updating query makes reference to table B and in the OLE DB Command task I can make reference to only one source of data.

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.

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

Regards

回答1:

The update in your SQL Command Task needs to look like this:

update Table A
set
col1 = ?
col2 = ?
WHERE SomeColumn = ?

Where "SomeColumn" is a column that uniquely identifies the row, and you'll need to have the value of "SomeColumn" in your dataflow so that you can map it to the third parameter in the SQL Command.

If necessary (judging from your original update), you can map multiple columns from the dataflow, in fact you can just use the original query in the SQL Command:

update A
set
col1 = ?
col2 = ?
where
? = value 1 and
? = value2 and
? = value 3;