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 .

Jetport

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.

Inodorous

hi i have read/ write access to this server, because i have created/edited notepad files and saved also. when i export the log text file i got this error- Error: 2013-07-24 17:14:31.13 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Please suggest – Jetport

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!

Fanchette

thanks for the suggestion Gowdhaman, i tried the same but still the problem persists. :( Please check my updated Question. – Jetport

Answer 3

Look into the childs' error detail under the main one saying "The job failed. The job was invoked...". In that child error u might find something like "Message ... To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report ..."

Affairs