All geek questions in one place

Creating an SSIS Package - Copying Data from Oracle to SQL Server

I want to write an SSIS package to copy data from an oracle server to an MS Sql server. as well as the condition: the data that needs to be moved from oracle to SQL will be known by selecting a table in SQL. Basically, Table1.Column1 on the SQL server should be populated from oracle if the value does not exist in this table in SQL Server. we also have a common column value in this table to map records to sql server and oracle. so for this I planned to do this in the SSIS package.

Can someone explain to me in detail how to create an SSIS package for the above scenario.

Thank you in advance for your help.

+6
source share
2 answers

You must use Lookup to join the common column (same data type) of the SQL server and Oracle and retrieve the values ​​from Oracle, which does not have a corresponding record on the SQL server.

Design Design

1. Using SSIS 2005

  • Drag the OleDB source and point to your Oracle database and select a table.
  • Drag the search and select the SQL Server connection and specify its table.
  • In the column tab, attach a common column and select the columns on the right side that you need to get. Lookup
  • Click Configure Error Output, and select the forwarding row for the join column. Lookup
  • Drag the Oledb command and connect it to the error exit from the search. 6. Write the insert statement in the Oledb command OledbOledb

2. If you are using SSIS 2008, then you do not need to configure the output of the error in the search. Just drag and drop the no match output from Lookup to the SQL Server destination.

+10
source

I suggest this implementation.

I used the praveen solution, but sometimes it may not be the best. Especially as a number if records at the destination grow. In addition, it does not apply to “updates”

+3
source

Source: https://habr.com/ru/post/917591/

More articles:


All Articles