SSIS package fails when scheduled with agent job

0
votes

I am trying to connect to Oracle using SSIS package (SQL Server 2008 R2) and loads data to my SQL Server table.

  1. Runs in a 64bit 2008 R2 machine
  2. Did Setup the TNS:Listner for Oracle client. I have both 32bit and 64bit Oracle client installed.
  3. Set environment variable path (TNS_ADMIN) pointing the TNSNAMES.ora directory of 64bit oracle client
  4. Created a registry entry "TNS_ADMIN" in HKEY_LOCAL_MACHINE-->SOFTWARE-->ORACLE

Everything works well when running in BIDS. I used Attunity connector and Native Oracle Provider for OLEDB. Both works in BIDS.

As soon as I schedule it in an SQL agent job and runs the same package, I get following error:

Message Executed as user: AMERICAS\ssisdata. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:17:23 PM Error: 2016-04-08 15:17:32.76 Code: 0xC0202009
Source: ORA OLEDB TEST Connection manager "OLEDB ORA CONN MGR"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12541: TNS:no listener". End Error Error: 2016-04-08 15:17:32.76 Code: 0xC020801C Source: Data Flow Task OLE DB Source [209]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDB ORA CONN MGR" failed with error code 0xC0202009.

I have tried in different machines. But same is the case. Breaking my head on this since 2 days. Any help would be appreciated.

1
sql-serveroraclesql-server-2008oracle11gssis
The error "ORA-12541: TNS:no listener" is pretty definitive. There is no oracle listener process on the ip address and port specified by the client that made the request. Period. Full stop. If you can get a successful tnsping from the client machine, then oracle is the victim,not the culprit and you need to go through your oledb configuration. Don't know what you mean bit "tns listener for client". The listener is a server-side process, not a client process. – EdStevens
On the server hosting Oracle type: lsnrctl, and then "status". If you don't see any actively listening processes there, type "start". Also, make sure your client tnsnames entry points to the right host and the right service. – access_granted
Thanks Chris. But I am trying same thing from same machine. If there is no Oracle listener process in the IP address specified, should the same package fail when running from Visual studio? It runs good when running from VS. But fails only in an SQL agent job from the same machine. About "tns listener for client", my bad, i actually meant is, the TNSNAMES.ora file specification. "tnsping" is nor recognized as a valid command from my machine. I don't know why am i getting that message, when I could connect successfully using VS. – Roby Skariah
Hi Access_Granted, Yes I have cross checked the client tnsnames entry and is perfect. In fact from the same machine, I am able to make it work when running from VS. Not just agent job. So the entries are right. I will need to check the lsnrctl status in Oracle server. Unfortunately it takes some time to get hold of the oracle admin to get these done. – Roby Skariah

1 Answers

0
votes

Did you do the Oracle setup on the server, as well as your development machine?

Also, when run on the server as part of a job, SSIS packages run as the SQL Server Agent service account. So you need to make sure that all your Oracle configuration (installation, security etc) is also valid for that account. The critical test is to log in to the server as the SQL Server Agent account (you may need a DBA/sysadmin to allow this user to log on interactively, as it often isn't granted this permission) and check that all your Oracle connectivity works under that user.

These kind of "works in dev, not on a server" problems are usually caused by the SQL Server Agent account not being able to access the same resources you take for granted as a dev user.