Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012.

I am doing it for the first time and every thing is fine i.e. all data from a particular table is being inserted into table in SQL Server.

But I want to place a condition i.e. if some record is new, only then add it in SQL Server. For e.g. there is an ID field in Oracle table which I can check in SQL Server table so if that particular ID is already present in SQL Server table then do not insert it.

I think this IF condition cannot be used in regular SQL and I have to use T-SQL which I have never used so how do I write this simple condition? Suppose following is table structure:

Oracle
Table: OracleTable
Fields: ID, Name

SQL Server
Table: SQLTable
Fields: ID, Name

So it will be something like:

SELECT * FROM OracleTable;
If (Oracle.ID != SQL.ID)
    INSERT INTO SQLTable(rest of the query here)

Or may be there is some option available in SSIS which will allow me to do this without writing query?

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

What you are looking for is described in detail in Andy Leonard's Stairway to Integration Services series. Feel free to bingle "incremental load SSIS" or take a peek at far too many of my answers on SO. This one describes our take on the incremental load pattern

share|improve this answer
add comment

assuming you can see the oracle table from your SQL Server

INSERT INTO SQLTable( ID, field2 )
SELECT ID,
       field2
FROM OracleTable o
WHERE NOT EXISTS( SELECT NULL FROM SQLTable s WHERE s.ID = o.ID )

this is just the simple SQL for inserts. I have not used SSIS so YMMV

share|improve this answer
    
I tried to use this in Lookup of SSIS but it didn't work. –  Frank Martin Nov 13 '13 at 17:19
    
Try this in an OLEDB destination. –  Renegrin Nov 13 '13 at 18:32
    
I added Lookup between Oracle and SQL Server inside Data Flow. So Oracle is connected to Lookup and Lookup is connected to SQL Server. Then I went into Edit mode of Lookup and in the Connection tab I selected my SQL Server connection in OLEDB connection manager. Below it there was an option to write query which I did as provided above but it says the table "OracleTable" doesn't exist. If I change the connection to my Oracle one then the Oracle gives error that table or view doesn't exist. –  Frank Martin Nov 13 '13 at 18:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.