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:

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?


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


assuming you can see the oracle table from your SQL Server

INSERT INTO SQLTable( ID, field2 )
FROM OracleTable o

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

  • I tried to use this in Lookup of SSIS but it didn't work. 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. Nov 13 '13 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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