6

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.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
Badri Prasad
  • 107
  • 1
  • 1
  • 9

2 Answers2

13

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 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 . Lookup
  4. Click on Configure Error output and select redirect row for the join column . Lookup
  5. Drag a Oledb Command and connect it to the error output from the lookup. 6.Write an insert statement in Oledb Command Oledb Oledb

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.

praveen
  • 12,083
  • 1
  • 41
  • 49
  • you don't need an OleDbcommnad, you can use a oledbdestination. I think it is easier and faster – Diego Jun 08 '12 at 09:27
  • hi Praveen, Thank you very mush it worked as i expected and it really helped me. One issue what i face is when i map a Column from sql server and oracle in look up. one of the column is of different datatype so it is not listing that is a column called 'ID' has to mapped but we have ID as int in SQL server and the mapping column for 'ID' in Oracle is in varchar. so how can i map it. – Badri Prasad Jun 08 '12 at 11:33
  • Also i want to clarify whether the look up functionality will work like this, first time when we run this it will bring all the data from oracle to sql since there is no data in sql. second time when we run it, from oracle only the data which is not there in sql has to be bring. is this how the lookup field will work. Please confirm this. thanks. – Badri Prasad Jun 08 '12 at 11:37
  • What datatype are you using for the column in sql and oracle ?? If the data types are not matching then you can use data conversion transformation to convert oracle data type to sql server data type Regarding lookup functionality ,when there is no data in sql server and you run it for first time all the data from oracle will be inserted into sql server since there is no match of data between the 2 tables – praveen Jun 08 '12 at 12:51
  • @BadriPrasad: When u run it for 2nd time it will it will only insert the data if it is not present in sql server . – praveen Jun 08 '12 at 12:54
  • Hi, Thanks for your answers. i want to know whether the Following is possible and if so please explain in detail. thanks. In SQL we have Table1 which has many Oracle server connection details. what we have to do is, we need to copy data from Oracle.Table5 to SQl.Table5. the condition is in SQL.Table5 i know the factory name and using the factory name i can get the oracle server details in SQL.Table1. so using this details i need to connect to oracle and get the matching data and then update in Sql.Table5. Is this possible in SSIS package? – Badri Prasad Jun 11 '12 at 07:16
  • @BadriPrasad : Please post it as a separate question. – praveen Jun 11 '12 at 08:10
3

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"

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Hi, Thanks for your answers. i want to know whether the Following is possible and if so please explain in detail. thanks. In SQL we have Table1 which has many Oracle server connection details. what we have to do is, we need to copy data from Oracle.Table5 to SQl.Table5. the condition is in SQL.Table5 i know the factory name and using the factory name i can get the oracle server details in SQL.Table1. so using this details i need to connect to oracle and get the matching data and then update in Sql.Table5. Is this possible in SSIS package? – Badri Prasad Jun 11 '12 at 06:53
  • @Diego I went thru Andy's blog .Thats the best implementation of Loading data into sql server .His additional Split transformation makes the difference with the solution which i have posted .I could have used a staging table to populate the updated data using Oledb destination and then in the control flow used an execute sql task to load it into destination table .in this way i can achieve better performance by avoiding row by row updation( which is done in Andy's blog using Oledb command) and perform a batch operation using Execute SQL Task – praveen Jun 12 '12 at 03:22