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 .
Advertisement
Answer
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 \server1itbillinkc whereas the SQL Server Agent Account might have it mapped to \server2domainAccountSQLServer 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 CORPCORP-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.