capture oracles's (sequence.nexval) value in a column during data flow...

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » capture oracles's (sequence.nexval) value in...

capture oracles's (sequence.nexval) value in a column during data flow...

Rate Topic

Display Mode

Topic Options

Author

Message

neeth

Posted Monday, September 10, 2012 12:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:28 AM
Points: 7, Visits: 163

Hi all SQL server techies.. I have the following scenario

I am doing an insert from sql server table to oracle table, which has a sequence for its identity column

I have used OLDDB command as my destination and used the following query for insert into oracle table

Insert into tablename (id, col1,col2....) values (seq.nextval,?,?,...) mapped all parameters to source columns and it works like a charm including sequence generation for identity column.

Here my another requirement is... sequence that was generated in first table have to be mapped with another table for foreign key relation... its like as follows

table 1

emp table with columns

empid ----------- generated from sequence
name
suffix

table 2

empinfo table with column

empinfoid ---- generated from sequence
empid ----- id that was generated in table 1.
address
edulevel

Since i do not have any others common keys between these two tables, i can not have lookup and pull empid from table 1 to table 2.

How can i insert empid (generated from sequence) into both tables in parallel

I tried with oledb command out parameter , but no success as oracle connection is unable to find output params..

Please help me in achieving this.. Have been googling for this for past few days, no suitable solution found

Thanks a trillion in advance..

Post #1356569