I am trying to connect to Oracle using SSIS package (SQL Server 2008 R2) and loads data to my SQL Server table.
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.
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.
User contributions licensed under CC BY-SA 3.0