Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

Question

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?

Answer 1

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

Answer 2

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

• Frank Martin: I tried to use this in Lookup of SSIS but it didn't work.
• Renegrin: Try this in an OLEDB destination.
• Frank Martin: 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.