SSIS package failing when scheduled to run as a job.

[mkal]

Server OS: Windows 2003 x64 sp2
SQL Server 2005 x64 w/SP3

I have a SSIS package (ProtectionLevelontSaveSensitive) that has been imported to Integration Services as a file. The file has not been stored in the default location during the package creation. No problems w/the import and when I execute the package from within the store it runs w/o error.

I schedule the package to run choosing "Package Source: SSIS Package Store" and setting the "Run as: SQL Agent Service Account".

The account that is running the SQL Server Agent Service is a sysadmin and belongs to the Administrators group on the local machine. It is also the same account that is running the Intergation Services and the SQL Server Service.

The job always errors out with the following msg:

Executed as user: Domain\user_account. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:00 AM Could not load package "\File System\Package_Name" because of error 0x80070005. Description: Access to the path 'C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Package_Name.dtsx' is denied. Source: mscorlib Started...

I've looked at the following KB article:

http://support.microsoft.com/kb/912911

and this one as well

http://support.microsoft.com/kb/918760

but have had no luck resolving my problem...any suggestions?

 

[NathanGriffiths]

Using SQL Server Management Studio, connect to the Integration Services instance where your package is deployed and expand the "Stored Packages > File System > " node.
Do you see your package listed there?

Sounds like your SQL Server Agent account might not have sufficient permissions to the "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\". Have a look at the permissions on that folder and if necessary grant the domain account you are using for SQL Server Agent "Read & Execute" permissions.

 

[mkal]

Yes the package is there. I can right-click on the package and it executes with out error.

Granted Full Control for domain\user_account on "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\", but I still get the same error msg.

I tried changing the owner of the package to domain\user_account and the computername to the name of the computer on which Integration Services is installed and that also did not fix the problem.

I can import the package into the MSDB store, schedule it as a job and it runs fine. However, I would like to be able to run them from out of the file system as well.

Any other ideas?

 

[NathanGriffiths]

Could be to do with the package protection level then. Does the package contain any connections that require a password? You might need to change the protection level to "EncryptSensitiveWithPassword" and then add the password into the Agent job step command line.

You might also want to have a look at this KB article which has some other scenarios that might be relevant.

http://support.microsoft.com/kb/918760

 

[MDXer]

This link should explain your issue and give all the options available to you.
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

 

[reginab]

You must change Protection Level to ServerStorage for the job to run as a scheduled package.

 

[mkal]

Thanks everyone. I believe this was my problem:

"The user account that is used to run the package under SQL Server Agent differs from the original package author."

So I logged onto the server as the account that runs SQL Agent Services. Opened the package, resaved, rebuild, re-import, then point the job to the new package within the msdb store. Not sure if this was the correct method for fixing this but it worked.