Monday, May 5, 2014

How to connect to Oracle Database in SSIS Pacakge

I was facing an issue in specifying the connection string to connect to Oracle database in a SSIS package.

Solved the problem by taking the following steps.

1) Download -  "Native OLE DB\Oracle Provider for OLE DB" from Oracle web site. Link for the same is - http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html

Make sure to click the option to install the plugins for Visual Studio. That will ensure that you get these options when creating the connection in SSIS Package.

2) Add - a new connection manager - using - the steps - "Connection Manager" --> "Provider -> Native OLE DB\Oracle Provider for OLE DB".

3) Specify the Server or file name in the following format -

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domainname.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=Service Name)))

Host = hostname.domainname.com
PORT=1526 = the port at which the Oracle database is hosted.
Finally - SERVICE_NAME=Service Name - Name of the Oracle database service.

Also you will need to specify the User Name and Password for the Oracle database. 





DBA should be able to provide you these details in case you are not aware of.

4) Finally you should ensure that the provider is installed on the server where the SSIS package will be deployed and that the server environment (32 or 64 bit) matches the server environment. Otherwise the package might face issues while running.

No comments: