Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS package for INSERT/UPDATE from Oracle to SQL server Table Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 1:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 102, Visits: 135
Hi ,

I need some info. about the Insert and updating the Records from Oracle Table to SQL server table using the SSIS packages and Attunity as the Source. Let me tell you my requirement

I have got a table in Oracle DB which has got a 'S_ID' and many Columns and 'DATEM' columns
so now i want to insert the new records and update the Exsiting records based on the Datemodified(DATEM) column into my sql table from Oracle..

I know that this kind of requirement are already available in net when i googled but i am unable to understand what to do and what are steps and what are the columsn to compare .

Please suggest me the ssis package and what are the transformations and any comparison columns i need to refer ?

Post #1596703
Posted Monday, July 28, 2014 1:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 1,964, Visits: 5,105
Guitar_player (7/28/2014)
Hi ,

I need some info. about the Insert and updating the Records from Oracle Table to SQL server table using the SSIS packages and Attunity as the Source. Let me tell you my requirement

I have got a table in Oracle DB which has got a 'S_ID' and many Columns and 'DATEM' columns
so now i want to insert the new records and update the Exsiting records based on the Datemodified(DATEM) column into my sql table from Oracle..

I know that this kind of requirement are already available in net when i googled but i am unable to understand what to do and what are steps and what are the columsn to compare .

Please suggest me the ssis package and what are the transformations and any comparison columns i need to refer ?



Since you are indeed doing a full merge, I would suggest to use a staging table for the ORA recordset and a stored procedure with the MERGE statement.
Post #1596705
Posted Monday, July 28, 2014 1:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 102, Visits: 135
Actually i am inserting and updating into the Staging table only from Oracle and then we are building a Dimensional Data warehouse upon that ..

But here i am Inserting and updating the Staging table only which we are going to use further.. :)
Post #1596706
Posted Monday, July 28, 2014 5:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 1,964, Visits: 5,105
Guitar_player (7/28/2014)
Actually i am inserting and updating into the Staging table only from Oracle and then we are building a Dimensional Data warehouse upon that ..

But here i am Inserting and updating the Staging table only which we are going to use further.. :)


Quick questions
a. Is it a large table?
b. Are there many new and updated entries?
Post #1596775
Posted Monday, July 28, 2014 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 102, Visits: 135
For some Tables
Yes they are some Big Tables and for some other Tables the insert and update may be huge
Post #1596778
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse