See link for installing Oracle client.
http://www.oracle.com/technetwork/database/features/instant-client/index.html
Another thing that I had to do because the SSIS package ran for more than 48hrs was to increase PGA limit of Oracle to 30GB from default 2GB because my session had to be open for a long time.
DETAILS
Prerequisites for this to be usual guideline
1. SSIS 2012 or later Installed
2. A connection to an SQL Server Database with a table with at least 1 million records (Source Table) for testing purposes
3. A connection to an Oracle Database, where your destination table will be contained
4. Oracle Client 11g or later installed in your machine
step 1:
GO into DataFlow Task
Step 2:
Select your Source component. In my case it is SQL Server. Usually you might have other transformation in between before your Destination component. In this case the focus is on the destination
Drag the ODBC Destination into the data flow like shown in the picture.
Double click on the ODBC Destination. (not shown) Then Click the 'New..' button to create an ODBC connection
You might see this page. if you don't see step 4. Anyway, Click the 'New..' button.. I see this page because I already created ODBC connection in my machine
Step 4:
You will be get another dialogue box for the Connection Manager. All you have to do is to Select the 'Use Connection String' Option and Click on 'Build;
Step 5:
Select the Machine Data Source. Click the New button (as always lol)\
step 6:
Select the Desired type of Data source. I prefer the machine Data source so that your package can be ran by any user on your machine. Click Next
step 7:
Scroll down. Skip the Microsoft ones, and Choose the Oracle in Oraclient11g or whatever name you used to install the Oracle Client in your machine, Then click Next. Then Finish
step 8
Enter the information as per your Oracle Database. The Data Source Name is up to you to define.
NOTE: If you plan to deploy your SSIS package to another machine make sure the Data source name matches the name on that machine so that when you install the driver on the other machine your package will not have validation error.. choose whatever configuration in the below box as per requirement. I felt mine with default setting.
Once you are done. You will eventually get back to this screen below
Step 10: Click a bunch of 'Ok' then select your ODBC Destination tables and start loading data.
This is just one way to do this. There are many other ways to install the Oracle ODBC connector.
like source/https://tensix.com/2012/06/setting-up-an-oracle-odbc-driver-and-data-source/
