I'm using -

Destination - Oracle driver - oraOLEDB.Oracle.1 (native ole db\oracle provider for ole db)

Source - SQL driver - microsoft ole db prover for sql server.

I want to import data from sql server to oracle. Challenge is, I have 1 million records on oracle. I have 100 records on sql server (these 100 records count will change daily). So, I thought of using 'lookup' task looking taking record from ms sql and fetch corresponding record from oracle. But when I use lookup, all records from oracle are loading into cache, which is taking approx 3 hrs. How do I resolve this?

Thanks,

Dinesh

• Edited by kdinuk 20 hours 42 minutes ago

Link the servers and then use the T-SQL Merge to sync the data. Or you can do it from the Oracle's side with its MERGE statement

Link the servers are not allowed.

http://www.developer.com/db/top-10-methods-to-improve-etl-performance-using-ssis.html

As arthur said, import the 100 records into a staging table on the oracle side then use plsql on the oracle server to merge the data into your large destination table - http://oracle-base.com/articles/9i/merge-statement.php

Another option if you want a only ssis solution is to get the MIN and MAX join keys of your 100 sql server rows and have your oracle lookup query filter the lookup resultset to be within the min-max keys only (be aware though that oracle and mssql sort chars differently, so this'd only work for an int/numeric key)

This way your lookup won't be caching the 1million rows, only the rows that might correspond to your sql source recordset. Obviously this falls over if your 100 sql server rows include join IDs 1 and 1000000