ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute Package Task always Fail When ExecuteOutOfProcess = False

Why does the Execute Package Task in SSIS always fail when set to execute in-process?

Having ExecuteOutOfProcess = False always results in that the package fails when run from within BI Studio.

I have also tried it from Command Line using dtexec., same results.

The problem with setting ExecuteOutOfProcess = True all the the time and running from within Bi Studio is that an enourmous amount of processes is created, none of which die because SSIS runs in Debug mode, another problem which is there doesn't seem to be a way to run it in Release mode so as not to get so many Dtexec processes, which ultimately results in having the Server die.

By Dying I mean, thrashing of the CPU & Memory. Page file to extreme limits.

And yes, the hardware should be able to take much more than what SSIS is supposed to eat. Dual 3GHz Xeon & 4GB of Ram.

Thank you.

My kind regards,

Eng. Ahmad Alkilani

Eng. A. Kilani wrote:

Having ExecuteOutOfProcess = False always results in that the package fails when run from within BI Studio.

What error message do you get?

Eng. A. Kilani wrote:

there doesn't seem to be a way to run it in Release mode so as not to get so many Dtexec processes, which ultimately results in having the Server die.

If you start the package using Ctrl-F5, BIDS starts one dtexec.exe process. When the package ends, just close this console window. I don't see how you can get numerous dtexec.exe processes. Do you mean dtshost.exe processes (these may be left behind for some time if parent dtexec.exe process is forcefully killed)?

|||Problems may already be solved, but for people like me who are looking for a solution to this problem: If ExecuteOutOfProcess is set to True bugs in the childpackage doesn't cause the parent package to stop because of this bug. It will also be possible for a single package to use more then 2GB of memory on it's own.

Execute Package Task always Fail When ExecuteOutOfProcess = False

Why does the Execute Package Task in SSIS always fail when set to execute in-process?

Having ExecuteOutOfProcess = False always results in that the package fails when run from within BI Studio.

I have also tried it from Command Line using dtexec., same results.

The problem with setting ExecuteOutOfProcess = True all the the time and running from within Bi Studio is that an enourmous amount of processes is created, none of which die because SSIS runs in Debug mode, another problem which is there doesn't seem to be a way to run it in Release mode so as not to get so many Dtexec processes, which ultimately results in having the Server die.

By Dying I mean, thrashing of the CPU & Memory. Page file to extreme limits.

And yes, the hardware should be able to take much more than what SSIS is supposed to eat. Dual 3GHz Xeon & 4GB of Ram.

Thank you.

My kind regards,

Eng. Ahmad Alkilani

Eng. A. Kilani wrote:

Having ExecuteOutOfProcess = False always results in that the package fails when run from within BI Studio.

What error message do you get?

Eng. A. Kilani wrote:

there doesn't seem to be a way to run it in Release mode so as not to get so many Dtexec processes, which ultimately results in having the Server die.

If you start the package using Ctrl-F5, BIDS starts one dtexec.exe process. When the package ends, just close this console window. I don't see how you can get numerous dtexec.exe processes. Do you mean dtshost.exe processes (these may be left behind for some time if parent dtexec.exe process is forcefully killed)?

|||Problems may already be solved, but for people like me who are looking for a solution to this problem: If ExecuteOutOfProcess is set to True bugs in the childpackage doesn't cause the parent package to stop because of this bug. It will also be possible for a single package to use more then 2GB of memory on it's own.