Calling child package out of process fails with Access Denied.

Has anyone ever used an Execute Package Task to call a child package, and the Execute Package Task's ExecuteOutOfProcess = True? Unless the account it runs under is an Administrator on the box, it fails for me with "Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_SalesTransaction_Tracking.dtsx". Access is denied."

This is eating up hours and hours of my time, time we can't afford. Is anyone able to successfully call a child package out of process?

Unfortunately I have faced the same problem.

This only happens when you're executing the package from SQL Server Agent, or your account is of lower priveledges than anything but Administrator.

The only current solution to it, until ofcourse someone from microsoft fixes this problem, is to have your package run under the privelleges of a user in the Local Admin Group. Anything, and I mean anything less than that will cause you the same problem. I tried enabling ever single option, one by one, in the Local Security Policy, and nothing but adding the user that the SQL Server Agent executes in the context of, adding that user to the Local Admin group was the only solution.

Sorry for the bad news. But that's probably your only current solution as to what I know of.

|||

In this case it appears that the SQL Server Agent user account does not have access to the location of the package to be executed. If that access can be granted, then that should be effective.

However, you may not wish to give access to the Program Files folder - so you may wish to save the packages elsewhere in the file system. Alternatively, if file system access is tricky to provide, you may wish to store the packages in SQL Server and execute them from there. There are specific SSIS roles which can be used to control access to packages. See this interesting article from Kirk Haselden: http://www.sqlmag.com/Article/ArticleID/46723/sql_server_46723.html or this entry in Books Online: http://msdn2.microsoft.com/en-US/library/ms141053.aspx

A solution which enables package access with least privileges is certainly preferable to adding your Agent user to Local Admin.

Donald