CODEWITHSUNDEEP
sql-serverssisodbcamazon-redshiftsql-server-agent
ptownbro
ptownbro

Reputation: 1284

SQL Server Agent SSIS Package AcquireConnection Error

I have a basic SSIS package that pulls data from a Redshift database ODBC DSN connection and puts it in a SQL Server database using an OLEDB connection. When I test the connections in Visual Studio everything is successful. When I run the package in Visiual Studio and the SSIS instance from SQL Server, they both are successful. However, when I try to run it from a SQL Sever agent job it fails with the AcquireConnection method failed error.

The VS solution/project is targeted to SQL Server 2014 same as the server.

The VS solution/project 64bitRunTime property is set to false so it will run as 32bit.

The configuration for the job step in SQL Agent is also set to 32bit.

Both the job owner and the step's login to the package are set to "sa" (just to test).

Still not working. Any ideas?

Upvotes: 1

Views: 485

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

The difference between cases is the user credentials. When you run the package - it runs under your credentials, when SQL Server Agent runs it - it (by default) runs under service account.
Ways to test and fix:

  • Create a SQL Credential, SSIS execution proxy and configure Job step to use it in run as dropdown.
    For testing - create a credential mapped to your account.
  • Change account under which SQL Agent starts or add privileges to the service account.

I opt for the first method, since it is more granular and affects only your job.

Upvotes: 2

Related Questions