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):
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].].
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