I found a similar thread posted under the same title as this by dba123. Unfortunately, his solution does not match my problem.

I have a SSIS package that will run fine in debug mode, will run fine when published to the server when I right click and execute it. However, when I run it through a job, either scheduled or running the job manually, the job fails. There is an entry in the servers application log telling me that "login failed for user xxxxx" where xxxxx is the name of SQL login that I am using. I know that the login works (logged in manually to the server via management studio, and it runs fine when I run it manually). Both connection managers are SQL, no flat files or other data source types. All the commands are T-SQL. The SQL Agent has full database access to both servers that the connection manager connects to - and its NOT the profile that is reporting as failing - it is a domain account.

This is totally bizzare - ANY help would be appreciated.

Jamie,

Curiously, I found the solution on another thread in this forum. It is to do with the protection level of the package, but also to do with proxy profiles and credentials. Oh, and yes, I do store passwords in my package.

Solution:

Firstly, create credentials and proxies:

1. Create a "Credential" under the "Security" node in SSMS for an existing account that has the appropriate rights to run the job.
2. Then in SQL Agent, under "Proxies" then "SSIS Package Execution" create a new "Proxy".
3. Now you need to go into your job and instead of running under the "SQL Server Agent" account you can choose your proxy. Now it should run! If you search for help on "Proxies" you can find more info (but not much more ).

Now temporarily change the package for deployment:

1. The default Protection Level setting for a SSIS package in BIDS is “EncryptSensitiveWithUserKey". This will allow for saving the package and running it in BIDS, so I maintained this setting while developing the package in BIDS.
2. When completed with creating and saving the package in BIDS, change the Protection Level in the package properties to "ServerStorage". The package will not be able to be saved in BIDS with this Protection Level setting. (this is a temporary setting, only for the purpose of saving the package to Integration Services, there is no intent to save the package to BIDS with this setting)
3. Do a mouse click within the Control Flow package graphic area to set it as the in-focus window. This is necessary, otherwise the option selected in the next step will not appear as an option.
4. Select "File" then "Save Copy of ", where is the name of the package. (do not select "Save ").
5. In the "Save Copy of Package" window, select "SQL Server" for the Package Location. After entering the server and security settings, select the instance name and enter a name for the package to be saved in the browse for Package Path. Select OK to save.
6. The package should now appear in Integration Services, after a refresh, under Stored Packages / MSDB / .
7. A SQL Agent job can now be created for the package, and the SQL Agent job should run the package without issue.

Thank you for your reply.

I have the same problem of a SSIS package running manually but not under SQL Agent. I followed your instruction and created a proxy, but the job keeps failing with the message "The package could not be found. The step failed."

Any ideas

Thank you in advance