Do the following steps to connect to Oracle in SQL Server Integration Services:

1.      Install ODTwithODAC1120320_32bit to see Oracle provider for OLEDB in Visual Studio

a.      Download URL : http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html (you need also to download and install ODAC 64-bit)

b.      This software will install Oracle client, ODAC drivers and Oracle provider for OLEDB driver in Visual Studio

c.       Remember the installation path, it will be used in the next steps

2.      Now you need to configure connections to connect to oracle

a.      Go to the installation path you have chosen while installing in step 1c

b.      Point to C:\~\product\11.2.0\client_1\network\admin\sample and open tnsNames.ora

c.       Copy tnsNames.ora to C:\~\product\11.2.0\client_1\network\admin

d.      Copy a sample connection string and replace with your oracle connection strings e.g. : <Oracle_Server_Name_Custom>= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <Oracle_Server_Name>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = <Oracle_Service_Name>) ) ) e. <Oracle_Server_Name_Custom> can be anything but its good practice to use <Oracle_Service_Name>

3.      Log off the computer and Login again (otherwise you will not see username and password option while using oracle provider for OLEDB in SSIS)

4.      Open any of the SSIS package, in connection manager > Right click > New OLEDB connection > Choose Oracle provider for OLEDB > Enter <Oracle_Server_Name_Custom> used in tnsNames.ora in point 3d, user name and password of the oracle > Test the connection

You need 32bit for SSDT and 64bit for executing the package as a job since SQL Server is 64bit.

Read my blog on using ODBC 32bit and 64bit drivers scenario.