How to make an Oracle Relation Connection in Informatica

This was one of the most challenging parts.

Problem – You need to make an Oracle connection at Informatica level so that you may use the same for read/write.

Solution

1.       For Linux and Windows , you need to Install Oracle client (admin version) freely downloadable from the OTN.

a.       Be careful about the bit compatibility, the oracle client and Informatica server bit should be same i.e. 64 bit or 32 bit

2.       Once you have installed the oracle client in the Windows/Linux box along with the Informatica server, you need to make the following environment variable entries:

1. PATH=$PATH/home/oracle/app/oracle/product/11.2.0/client_1/bin
2. ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
3. ORACLE_BASE=/home/oracle/app/oracle
4. LD_LIBRARY_PATH=/home/oracle/app/oracle/product/11.2.0/client_1/lib
5. TNS_ADMIN=$ORACLE_HOME/network/admin

Preferably you may make these entries at Informatica Administrator>Repository Services>Process tab>Env variables level.

3.       Now to connect to oracle database from Informatica, we can either use ODBC connection or Oracle connection.

a.       Connecting using Oracle drivers

                                                               i.      Make the TNS entry in the tnsnames.ora file,assuming the connection string name=Conn_string

                                                             ii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make Oracle connection for TNS entry

2.       Type in the credentials and make the connection string as  Conn_string same as TNS entry.

b.      Connecting using ODBC drivers (Approach not applicable for Linux only for Windows)

                                                               i.      You need to go to> Start>Administrative Tools>ODBC Data Sources>System DSN

                                                             ii.      Make a new System DSN give the DSN Name as Conn1

                                                            iii.      At the workflow manager level you need to Connections>Relational Connections>Make new Connection

1.       You will have option of Oracle or ODBC connection, make ODBC Connection

2.       Name the Connection String as Conn1

                

PMWS_33016--Informatica Authorization error

I have often faced difficulty in configuring a WSDL connection in the workflow manager.Hence listing down the steps:

1. Import the WSDL source/target as Import>WSDL Consumer.-Designer
2. navigate through the options of the import.-Designer
3. Make an application connection in workflow manager as a Web Service consumer connection.
4.Make sure of setting the connection authentication mode as Basic and not PEM. 
5.Enter username and password, Domain need not be entered.

Talend vs Pentaho (ETL) review

Informatica happens to be the current leader in the ETL stack, but as a result of rapid business change, the demaqnd of light weight tools for the Small and medium size industry has increased exponentially.This sector is very particular on cost and would need a tool to address its data migration activities across different environments.High end tools like Informatica have thus released their thinner version i.e. Informatica Powercenter express to address such clients, which generally have data migration requirements. It is one time, across heterogeneous sources and very tight on cost.
We shall be reviewing Talend and Pentaho ETL tools which belong to the same space.

Talend

I installed the latest version for talend 5.5.1 and tried exploring the same. My reviews are as follows:

Pros
1. 30 day free trial version available.
2. Server - 2 GB
3. Free and comprehensive documentation.
4. Exceptional tool for people coming from Java coding background. The ETL concept is partially GUI based (unlike Informatica) and uses a lot of Java code to implement data transformations. Good tool for coders who want to learn ETL.
5. No database required for repository metadata.
6. Integration with a wide variety of storage systems including-Amazon Web Services' RDS, Big Data,WSDL etc.

Cons
1. Bit complicated to adopt for people who have worked on complete GUI based ETL tools like Informatica
2. The help center is available for 4 countries only, so your server should reside in one of these countries for you to access the same. India is not one of those countries :D

Pentaho

After my tryst with Talend, I decided to explore the data Integration suite of Pentaho.Reviews are as follows:

Pros
1. Very easy to install and use
2. Comprehensive documentation (better than talend)
3. Less coding more of GUI.
4. No database required for repository metadata.
5. Custom output that can be used by the Reporting tool of Pentaho.

Cons
1. No direct integration with WSDL.

 

The Snow cap on the peak of frustration

The situation is similar to the second session of the third day of the test match.A batsmen well settled has to face the vicious spinner , trying to find a way through him. The fielders surround the batsman in the scorching sun, trying to mentally disintegrate him through sledging.

An IT job has a very similar situation when you are about to complete your second year. It is the time when you are on the verge of expanding your comfort zone within the organisation. You have successfully survived the viciousness and discouragement of your insecure manager and colleagues and ended up with decent appraisals. Impossible tasks are thrown upon you now and you are left with a feeling of whether to continue with the company.

1. A good decision would be have an offer in hand.
2. If you dare to  handle different kind of tasks thrown at you and are able to survive the same in your 3rd year, this could be your road to fame and growth within the organisation,which you had been eagerly waiting to walk all these years.
3.I had missed a similar opportunity in my first company at the end of  years when I had put down papers there.
4. Always remember - Never give in. (Cornwallis house motto), hold the fort and thou shall walk the path to glory.