CODEWITHSUNDEEP
sqlsql-servert-sqlssissql-agent-job
rkapukaya
rkapukaya

Reputation: 77

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/

Upvotes: 2

Views: 5963

Answers (5)

Dancun Gerald
Dancun Gerald

Reputation: 57

For me I opened Services on the windows search menu then located my sql server db instance and sql server agent services. I right clicked on each one of them and selected 'Properties' option. Under the properties option select the "Log On" tab and change the "Log on as" to 'This account'. Provide your local machine name and enter the password you use to login to your machine as the password. Once that is done restart the services for the changes to take effect. Do this for both the SQL server and SQL server agent service.

I have a couple of SQL servers installed but below is a screenshot of what I'm refering to. I hope this helps. enter image description here

Upvotes: 0

Sanushi Salgado
Sanushi Salgado

Reputation: 1446

In my case, the SQL job failed because an OLE DB connection manager inside the SSIS package was using the incorrect log on to the server & I had to change it from SQL Server Authentication to Windows Authentication. That fixed the error in my SQL agent job.

Upvotes: 0

wvmitchell
wvmitchell

Reputation: 119

If you have a password in your SSIS package, make sure that the ProtectionLevel is set to "EncryptSensitiveWithPassword". If you use "EncryptSensitiveWithUserKey" then you'll be the only user who can run it.

Upvotes: 2

somaye javidmoradi
somaye javidmoradi

Reputation: 197

does your package of SSIS any password? if not,set password for it .then edit your job and write password there too.

Upvotes: 2

Bill Jetzer
Bill Jetzer

Reputation: 1115

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.

Upvotes: 3

Related Questions