Search code examples
sqlsql-servert-sqlssissql-agent-job

SSIS package fails when runs by sql job agent

I am in trouble with running SSIS packages in my etl process.

There is a table that includes etl steps with different types. i.e. Stored Procedure steps, cmd (copy-move files) steps, sending mail, SSIS steps.

A main Stored procedures runs these steps according to type of steps and logs begin-end times, errors etc. This main stored procedure is triggered by an sql server job.

When I scheduled this job, SSIS steps takes error which is below;

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'server\user$'.".

Yet, when I run this main procedure manually (with my windows user login) it runs successfully.

I checked this running with proxy account, it didn't work for me. Because inside the sql server job, i run my main stored procedure not SSIS package.

proxy account reference link; https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

Solution

  • It should work so long as your connection to the database is valid. The error message is telling you that server\user$ is not a valid login.

    If the SSIS Package is connecting to the DB with a username and password (i.e., a SQL user), then it should be straight forward to set up / configure the login to have access to the server and database where your proc resides, and when you next run the job it should be able to successfully execute the proc.

    If instead the SSIS Package is connecting with Windows Authentication, then you'll need to use a Proxy that specifies a Credential that references a Windows / Active Directory account with database access. In SSMS:

    1. Server >> Security >> Credentials
      • create a new credential
      • give it a name
      • specify a windows account that has permissions to execute your proc
    2. Server >> SQL Server Agent >> Proxies >> SSIS Package Execution
      • create a new proxy
      • select the credential you just created
      • Select "SQL Server Integration Services Package" subsystem
    3. Server >> SQL Server Agent >> Jobs >> Your Job >> SSIS Job Step
      • in the "Run as" field, pick the proxy you just created

    Now when you kick off the job, your SSIS package should make a connection to the database using the windows account specified in the credential set up in step 1, which should be able to successfully execute the stored proc.