SQL Agent Failing when trying to run my SSIS Package


SQL Agent Failing when trying to run my SSIS Package

by WiltsDB » Mon, 08 Oct 2007 02:33:17

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 Thomso

SQL Agent Failing when trying to run my SSIS Package

by Jamie Thomso » Wed, 10 Oct 2007 03:34:18

This is just a shot in the dark but what is the ProtectionLevel property of the package set to

And, do you store passwords within the package



SQL Agent Failing when trying to run my SSIS Package

by BigSquar » Thu, 11 Oct 2007 05:36:20

If you use windows authentication for any connection in the package then the package will run with the default protection level from SQL Server Agent - as long as the SQL Server Agent service account has the permissions required to run the package of course.

This also means you can keep the packages as dtsx files instead of having to save them to the SQL Server - unless you want to do this for other security reasons.


SQL Agent Failing when trying to run my SSIS Package

by WiltsDB » Fri, 12 Oct 2007 04:35:19


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.


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.

Massimo Sartori

SQL Agent Failing when trying to run my SSIS Package

by Massimo Sartori » Sat, 13 Oct 2007 06:37:21

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


SQL Agent Failing when trying to run my SSIS Package

by Paminbosto » Sun, 14 Oct 2007 08:39:23

Thank you, WiltsDBA! You're advice was right on.

One gotcha that I encountered...when setting up the Proxy, don't forget to add the SQL Agent account as a principal. I did that. It wasn't pretty. Smile


SQL Agent Failing when trying to run my SSIS Package

by RC_SSI » Mon, 15 Oct 2007 07:38:22

I was having a similar problem but the error I was receiving was "The package could not be loaded." The package protection level is set to EncryptSensitiveWithPassword.

Apparently the package needed to be executed with the creator's credentials. I resolved this by creating credentials and a proxy that pointed to my credentials and the SQL Agent was then able to execute the job without any problems.

My question is, is this "best practice" I know it works, but is there a "simpler" way, i.e. allow the Network Service to execute so a proxy isn't required

Obviously I still have a lot to learn, and would appreciate any guidance as to where I can find out more about what is exactly happening here.