Newbie: How to update SQL table from Oracle table using SSIS

Question

Newbie: How to update SQL table from Oracle table using SSIS

conningtower

Say Hey Kid

Points: 675
More actions
October 2, 2009 at 9:19 am

#135543

Hi All
I m trying to access an Oracle table and update a sql table based on a primary key match between the two tables.
What I ve done so far : OLE DB Source (SQL) --> Lookup (Oracle table) --> OLE DB Command (Update sqltable set sqltable.field1 = (select oracletable.field1 where oracletable.primarykey = sqltable.anotherfield ) )
This is the only way I could think of to do this. i d really appreciate it if someone could tell me the correct way to this.
Thank you
Gomathi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gsatpal 82862 SSC Journeyman Points: 88 More actions · Answer 1

i'm trying to do the exact same thing.

so far i have an OLE DB Connection, LookUp (Expression), Conditional Split, OLE Destination for Insert New Rows, and an OLE Command for Update statements.

So far, it works for the inserts but not for the updates. I think i may have wasted my time with Oracle Updates, reading that sometimes they dont support parameterised statements from one datasource to another.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 2

conningtower (10/2/2009)
Hi All
I m trying to access an Oracle table and update a sql table based on a primary key match between the two tables.
What I ve done so far : OLE DB Source (SQL) --> Lookup (Oracle table) --> OLE DB Command (Update sqltable set sqltable.field1 = (select oracletable.field1 where oracletable.primarykey = sqltable.anotherfield ) )
This is the only way I could think of to do this. i d really appreciate it if someone could tell me the correct way to this.
Thank you
Gomathi

I actually think this is a pretty good way to do it assuming you aren't interested in capturing history.

I also assume that if a PK doesn't look up then you are inserting it as a new record?

My only suggestion would be to get rid of the OLE DB Command. Instead, take records that lookup (Updates) and insert them into a stage table. Then use a Execute SQL task to perform a set based update joining on your PK. This will increase performance.

gsatpal 82862 SSC Journeyman Points: 88 More actions · Answer 3

i got round mine by creating a temp table, loading all "updated" rows as an insert into the temp table.

then in the flow, add an Execute SQL Task. blah blah blah, then delete the temp table/rows. long winded, but it does the job.

NOTE: for oracle tables, its near enough impossible to use a SQL Command to update the table with an update command. i think there is an issue with the Microsoft Oracle Driver, which doesnt allow parameterised commands.