November 22, 2007 at 12:54 am
So I'm building this package in which records in a dataset either need to be updated(existing) or inserted(new). I use a Conditional Split to seperate the records, which works fine. The split is like WHERE field1 = 1 to INSERT records, value 2 is UPDATE existing records.
According to several articles I've read you should/could use the OLE DB COMMAND to do the update. I add this component and add an update statement, something like this:
UPDATE table1
SET Field2 = 'ABC'
I expect this wuld be enough, because the Conditional Split should send ONLY records to be updated into this flow. But with the above statement ALL records in the destination table are updated; I need to add WHERE Field1 = 2.
How can I use the OLE DB COMMAND so, that ONLY the record send by the Conditional Split are updated?
TIA,
Greetz,
Hans Brouwer
November 22, 2007 at 2:02 am
Hans Brouwer (11/22/2007)
So I'm building this package in which records in a dataset either need to be updated(existing) or inserted(new). I use a Conditional Split to separate the records, which works fine. The split is like WHERE field1 = 1 to INSERT records, value 2 is UPDATE existing records.According to several articles I've read you should/could use the OLE DB COMMAND to do the update. I add this component and add an update statement, something like this:
UPDATE table1
SET Field2 = 'ABC'
I expect this would be enough, because the Conditional Split should send ONLY records to be updated into this flow. But with the above statement ALL records in the destination table are updated; I need to add WHERE Field1 = 2.
How can I use the OLE DB COMMAND so, that ONLY the record send by the Conditional Split are updated?
TIA,
No, it must be:
UPDATE table1
SET Field2 = 'ABC'
WHERE Field1 = 1
or
UPDATE table1
SET Field2 = 'ABC'
WHERE Field1 = ?
and add Field1 as parameter for the query.
OLE DB Commnad does not know about conditional split, it work as T-SQL command, when WHERE clause is not specified then it work for all rows in the table.
November 22, 2007 at 2:52 am
Tnx for answering. I suspected as much. Makes me wonder in this case why I need to use a Conditional Split to seperate the records. I could direct them into a temporary table and then do udates from there, but that's a lot of overhead for just a few records...
Greetz,
Hans Brouwer
November 22, 2007 at 3:25 am
Inserting records is handled by OLE DB Destination, updating is handled by OLE DB Command. So it must be separated by conditional split or any other component which has splitting capability(for example Lookup - Error Output (red line) gives the way for handling inserting, Data output (green line) gives the way to handle updating).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply