Configuring/Create PowerCenter Connection to a SQL Server instance via jdbc

Question

I am working in a project where I need to integrate data coming from Oracle DB instance and SQL Server.

All was fine till I runned the workflow and I got the ORA-009242 error that told me that the schema(on the SQL Server) was not found.

After few search I realized that though I was able to get the same schema(actually a single table) in the Source Analyzer with a ODBC connection, for getting the data I need to use a JDBC.

That would not be a problem if I could use the Informatica Developer, where any kind of connection can be configured,but from there I am getting that I miss the driver(I guess I have to add it locally, though I added at the server level where Informatica is installed) but unfortunately I am not able to setting/create such connection in the PowerCenter workflow. Unfortunately I am binded to such and cannot develop the project into the Informatica Developer.

Moreover I already set a Native MSSQL server with JDBC string connection configured(tested and working) from the Informatica Developer that can be seen into Informatica Administration pannel(as for all of them as stated into Informatica online help). Moreover I did have put the jdbc driver into the appropiate folder(D:\Informatica\10.1.0\server\bin\javalib).

After a few search I was able to find only this post:

JDBC connection in Informatica Powercenter

, where it is clear the only work around is to use a Java transformation as suggested from the support,but is limited to mapping(where in case of teh SQL Server I did not have problem connecting to the source via ODBC):

enter link description here

JDBC Driver Class Name : com.sqlserver4.jdbc.Driver Connection String: jdbc:sqlserver4://myslqserverinstance:1433;SelectMethod=cursor;databaseName=DBName What I am missing?

There is a way to use the connection create at the administration pannel into the PowerCenter Workflow? Or as second option use the one created at developer?

For information I am using:

Windows7 Informatica PowerCenter 10.1 JDBC 42

Thanks

P:S using an ODBC as I did in the source profile and as first attempt gave me the error:

Severity Timestamp Node Thread Message Code Message ERROR 13.12.2016 08:01:03 node01 READER_3_1_1 RR_4036 Error connecting to database [ [Informatica][ODBC SQL Server Wire Protocol driver][SQL Server]Die von der Anmeldung angeforderte Cognos-Datenbank kann nicht geöffnet werden. Fehler bei der Anmeldung. SQLSTATE: 08001 [Informatica][ODBC SQL Server Wire Protocol driver][SQL Server]Fehler bei der Anmeldung für den Benutzer 'COGNOS'. SQLSTATE: 28000 Database driver error... Function Name : Connect Database driver error... Function Name : Connect Database Error: Failed to connect to database using user [Cognos] and connection string [dehze01-wcs008\mstore].].

Why do you need to use JDBC? Can't you create a ODBC or SQL server connection object in workflow manager? — Samik
That was the first try. it does not work. Indeed I am getting this error, I added. — Carmine Tambascia
Schema not found means that either you don't have permissions to a schema or that you are using the wrong schema and table name. Check SQL Server's log for possible error messages, or SQL Server Profiler to capture the entire connection and query sequence. Can you connect to the database with your user, using eg SSMS? — Panagiotis Kanavos
Yes make sense, but I do used the same parameters(user name and passwords, when I did the import of the table in the source analyser, so that cannot be, i do have privilages and I do know the DB name. HAs I wrote I already configured the connection in the Informatica admin pannel and in the Informatica Developer. I did not tried to connect to the DB with SSMS, but as wrote above I need to do this in PowerCenter. The job has to be develop there and I need to use some data from this SQL Server — Carmine Tambascia
Actually for the connection set into Informatica developer I got the message telling me its missed the driver(I suppose I have to put the jar file also at local level), but as said from the admin of Informatica the same connection is working — Carmine Tambascia

Carmine Tambascia Carmine Tambascia · Accepted Answer · 2016-12-14T11:39:20

After have contacted the Informatica's Global Support few weeks ago, I realised that I made some really basic mistake due confusion on how I connected to Oracle instance instead.

The right steps for connection a Powercenter 10.1 to SQL Server Instance are:

Create an ODBC connection on the Server (in my case Windows Server 2012) under the ODBC 64bit interface and create a new connection under System DNS(so it will be available for all users).
The protocol that worked for me is the DataDirect 7.1 New SQL Server
Keep track of the name you assigned to the connection.
Once in the Workflow Manager create a connection and choose ODBC tipe, fill in all the paramenter using for the 'Conncting String' the name used in the Server for the same connection.

At the moment I cannot recall the mistake I was making, even using not the right name or the right type(mostly I was using a Native SQL protocol probably with incorrect parameters

Configuring/Create PowerCenter Connection to a SQL Server instance via jdbc

1 Answers