2

I have a package in SSIS and it only contains an Execute SQL Task with a connection to Netezza. So the provider to connect to Netezza that I use is NZOLEDB. When I run it locally on my machine, it runs smoothly and without error. However, when I deploy it in SQL Server and execute it with a Server Agent, I get 2 errors:

1) Failed to acquire connection "Connection name". Connection may not be configured correctly or you may not have the right permissions on this connection

2) SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Netezza OLE DB Provider" Hresult: 0x80004005 Description: "Client unable to establish connection".

In the job step, I went to Configuration > Connection Managers and manually added in the value for the password to connect to Netezza. That still didn't help.

Any help would be great, thanks!

jim
  • 718
  • 2
  • 9
  • 26
  • Does the SQL Agent have permissions to `Netezza`? Usually when you run something locally and it works, that's using your credentials. When you deploy to SQL Server, it will run as the service account. – SS_DBA Jan 31 '19 at 19:24
  • 1
    @WEI_DBA thanks for the response. All I know is that we installed the proper drivers to the server (NZOLEDB) however why would the service account need its own account to access Netezza? Can't it just use my credentials that I'm specifying in the Package? – jim Jan 31 '19 at 19:35
  • Good point. If you store the credentials in connection manager in the package, it should use that. But past experience, I was only stating what I've seen when deploying anything to the server, it will use the service account to run. In the SQL Agent job, are you specifying your account to run under? – SS_DBA Jan 31 '19 at 19:40
  • @WEI_DBA yes I am. I make sure I specify the connection string and password related to my credientials for the connection manager under SQL Agent job – jim Jan 31 '19 at 19:41
  • Hi @jim, did you know you can google SSIS Error codes and usually you will find the answer, or at least a better explanation of the code. I found this SO question after about 3 seconds of searching. Looks like it could be related to your situation. https://stackoverflow.com/questions/21603897/dts-e-oledberror-error-code-0x80004005-difference-between-sql-native-client-an – digital.aaron Jan 31 '19 at 22:44

0 Answers0