September 16, 2009 at 11:51 am
Hi Everyone
I m a SSIS newbie.
I m faced with a situation where
1. I have a table in Oracle that I use to Update another table in Sql Server 2005
2. I have used Lookup transform to join the Primary key and Foreign key in both the tables.
3. Where I m stuck is I dont know what component to use to update my destination table when the OracleTable.Primarykey = SqlTable.Primary .
4. I tried using Merge & MergeJoin , but it adds new rows into my destination table rather than updating the existing destination table rows.
Please Help.
Thank you.
Gomz
September 16, 2009 at 12:31 pm
You could use an OLEDB Command transformation to do the update for you.
Or you could send the rows to a staging area and then run an UPDATE to do them as a batch.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2009 at 1:53 pm
You could use an OLEDB Command transformation to do the update for you.
Exactly.. You use the lookup to find out if the record exists, there is a little bit of a trick here. If the lookup finds the record it will send it out the "Lookup Output", you use this to feed the OLEDB Command component. You configure the error output as redirect row, this handles the records that don't exist, these you can feed into a OLEDB Destination. As an additional note, the OLEDB Command should have logic to not do an update where nothing has changed. I would only ignore this if there are not a lot of changes otherwise it will take some time to apply them..
CEWII
September 17, 2009 at 10:09 pm
Thank you very much for your prompt replies , I was able to use OLE DB Command to get what I wanted thanks to u both.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply