The job failed. The job was invoked by user<user>. The last step to run was step1

Question

I have created SSIS package which is running successfully and dumping the data to the required place. But the same package results in the error when i run it through job. I googled n got these links but failed to get the way out- http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/9034bdc4-24fd-4d80-ad8d-cc780943397a/

http://www.progtown.com/topic390755-error-at-start-job-the-job-was-invoked-by-user-sa.html

Please suggest .

Answer 1

The screen captures are great but the detail is going to be on the sublines, so in the first picture, where you have expanded the [+] sign and it says "Executed as user X. Unable to open Step output file"

If you select that row, there is generally more detail displayed in the bottom pane.

General trouble shooting for something working in BIDS/SSDT but not in SQL Agent

That said, generally when something works in BIDS/SSDT and does not in the SQL Agent, then the first thing to look at is the difference in permissions. You are running the package in visual studio and your credentials are used for

• File System
• Database (unless a specific user and pass are provided)
• General SaaS (Send Mail Task will use some mail host to transfer the email)

Running things in a SQL Agent job can complicate things as you now have the ability for each job individual job step to run under the SQL Agent account or a delegated set of credentials your DBA has established.

Further complicating matters are network resources---my K: drive might be mapped to \\server1\it\billinkc whereas the SQL Server Agent Account might have it mapped to \\server2\domainAccount\SQLServer\ or it might be entirely unmapped.

As Gowdhaman008 mentioned, there can also be a 32 vs 64 bit mismatch. Generally this is specific to using Excel as a source/destination but also rears its head with other RDBMS specific drivers and/or ODBC connections for said resources.

Specific to your example

Based on the fragment of the error message, my primary assumption is that the account CORP\CORP-MAD$ does not have access to the location where the file has been placed. To resolve that, ensure the MAD$ account has read/write access to the location the Happy files have been placed. Since that account ends in $, it might only exist on the computer where SQL Agent is running. If it's accessing a network/SaaS resource, you might need to create an explicit Credential in SQL Server (under Security) and then authorize that Credential for SSIS subtasks.

A secondary, less likely, possibility is that the files don't exist and that's just a weird Send Mail error. I know I still get plenty of hits on The parameter 'address' cannot be an empty string even though an email address is provided.

Answer 2

I am assuming that it is running in BIDS, not in SQL Agent job. I faced this kind of problem and set the package property in the agent job as following screenshot[checked the Use 32 bit runtime] and it worked for me.

Hope this helps!