[ARCHIVED] Is anyone using Informatica PowerCenter to connect to the hosted data?

[auerbach]

The University of Michigan is hoping to connect to the hosted data for ETL (Extract-Transform-Load) with Informatica. We know that Informatica Cloud users can connect to cloud data providers, but we aren't clear on how to accomplish this with the on-premise PowerCenter tool. Is there anyone else who has done this and could offer guidance?

Thanks!

Glenn Auerbach

University of Michigan

[msmith]

I found this just Googling around:

Amazon Cloud Integration & Connectors | Informatica U

Does it look like something you could take advantage of?

*edit - actually reading through it, it looks like the same Informattica Cloud solution you mention already.

[msmith]

I mentioned this on the office hours call today, but will write it here for completeness..

One thing you could try is connecting to the Redshift database as a PostgreSQL data source.  Redshift is built using PostgreSQL on the front end.  Many PostgreSQL clients will work with Redshift.  I would attempt it both via ODBC PostgreSQL drivers and via any native support for PostgreSQL that Informatica might have

[msmith]

This slide deck seems to hint that Informatica supports using PostgreSQL ODBC to connect to Redshift:

https://community.informatica.com/mpresources/docs/AWS-Redshift-FAQs.pdf

[rubyn]

Still erroring out.  This is from our developer, any help is appreciated.

I finally got it to connect from designer and was able to get the tables loaded into my mapping as sources using the ODBC driver. 

Then to run the workflow to get data into a target table, I changed the kiwi's ODBC.ini file and made an entry there.  I am logging in as the same user/pw to get the sources that were provided to me.

At the top:

PGRESQL_SRC_Redshift_Test=DataDirect 7.0 PostgreSQL Wire Protocol

In the body:

[PGRESQL_SRC_Redshift_Test]

Driver=/home/kiwi02/app/etl/Informatica/PowerCenter9.5.1/ODBC7.0/lib/DWpsql26.so

Description=DataDirect 7.0 PostgreSQL Wire Protocol

Database=canvas

HostName=52.5.214.139

LogonID=

Password=

PortNumber=5439

When I run my workflow, it errors out with:

Error connecting to database [

[unixODBC][Informatica][ODBC PostgreSQL Wire Protocol driver]Can't connect to server on '52.5.214.139'[unixODBC][Informatica][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.

Database driver error...

Function Name : Connect

Database driver error...

Function Name : Connect

Database Error: Failed to connect to database using user [canvas] and connection string [PGRESQL_SRC_Redshift_Test].].

[msmith]

This looks like a connectivity problem. Can you verify that the server making the request has connectivity on port 5439 to 52.5.214.139.  If you would like us to double check the whitelist for the IP address for this server, please send it to us.  thx.

[auerbach]

As an update, we were able to use a generic Postgresql connector to read and download data as part of an Informatica ETL process. Performance was good and our testing has demonstrated good enough reliability to use at an enterprise level.