Create a SSIS Package - To Copy data from Oracle to SQL Server

Question

I want to write a SSIS Package for copying data from oracle to MS Sql server. and also the condition is, What are the data has to be moved from oracle to SQL will be known by selecting a table in SQL. Basically Table1.Column1 in SQL server has to be filled from oracle if the value is not there in that table in SQL Server. we have also have common column value in that table to map the record with sql server and oracle. so to do this i have planned to do it in SSIS Package.

Can any one explan me in detail how to create a SSIS Package for the above said scenario.

Thank you in advance for your help.

Answer 1

You should use Lookup to join the common column( same data type) of SQL server and Oracle and retrieve the values from Oracle which does not have a matching record in SQL server.

Design

1.Using SSIS 2005

1. Drag a OleDB Source and point to your Oracle Database and select the table .
2. Drag a lookup and select the SQL Server connection and point to its table .
3. In columns tab join the common column and select the columns in the right hand side which you need to retrieve .
4. Click on Configure Error output and select redirect row for the join column .
5. Drag a Oledb Command and connect it to the error output from the lookup. 6.Write an insert statement in Oledb Command

2.If you are using SSIS 2008 then no need to configure the Error Output in lookup .Just drag the no match output from Lookup to SQL Server destination.

Answer 2

I suggest this implementation.

I have used praveen's solution, but sometimes it may not be the best. Specially as the number if records on the destination grows. Also, it doesn't deal with "updates"