Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Should you execute child packages in-process or out-of-process?

SQL Server Integration Services (SSIS) functionality is wrapped up in packages. A package is the SSIS executable. A package can contain a task called the "Execute Package Task" which enables you to execute a different package from within a currently executing package. The package containing the "Execute Package Task" is referred to as the master package. The package being called is referred to as the child package.

The "Execute Package Task" provides the capability for a child package to be executed in-process or out-of-process by use of the ExecuteOutOfProcess property. A child package that is executed out-of-process will result in a new process being started in which the package is executed. To quote BOL, the ExecuteOutOfProcess property is used to "Specify whether child package runs in the process of the parent package or in a separate process".

If a child package is executed out-of-process, the resultant OS process is called dtshost.exe. So, if you are executing many child packages from within BIDS using debugging you should be able to see many instances of dtshost.exe running in Windows Task Manager and these processes will remain "live", using up resources, for quite a while after execution is complete. I have witnessed shutdown times of minutes rather than seconds when many of these processes are executing. Note that this is in part down to the vagaries of managed code and the problem is not quite as prevelant when the master package is executed from BIDS without debugging.


I recently raised a question on the beta newsgroups relating to whether or not a child package should be executed in-process or out-of-process. Michael Entin from Microsoft provided some excellent answers. To summarise here:

-If executing in-process, a bug in a task of the child package will cause the master package to fail. Not so if executing out-of-process.
-On 32bit systems a process is able to consume up to 2GB* of virtual memory. Executing out-of-process means each process can claim its own 2GB portion of virtual memory.

Michael therefore recommends that if you are simply using many packages to struture your solution in a more modular fashion, executing in-process is probably the way to go because you don't have the overhead of launching more processes. I'm happy to pass on that advice here.

-Jamie


*3Gb if you boot with /3Gb switch in boot.ini

Published Monday, May 16, 2005 8:55 AM by jamie.thomson

Comments

 

Jamie Thomson - Life, the universe and SSIS! said:

I've been working with SQL Server Integration Services (SSIS) for quiet some time now and as time...
March 24, 2006 10:31 AM
 

Only Talking Sense » SSIS - ExecutePackage said:

August 13, 2006 9:07 PM
 

SSIS Junkie said:

I've been working with SQL Server Integration Services (SSIS) for quiet some time now and as time goes

January 16, 2007 6:18 PM
 

Jeff Jordan said:

We've noticed that in a 64-bit environment (x64) the 64-bit dtexec.exe calls the 32-bit dtshost.exe when ExecuteOutOfProcess is true.  There seems to be a 64-bit version of dtshost.exe installed in the expected directory.  Is there any reason that dtexec wouldn't use it?  Also, is there a way to explicitly set which exe will be started?

Thanks,

Jeff

August 27, 2007 1:30 PM
 

Nick Van Dyk said:

I am noticing the same thing as Jeff.  When I run my packages in process I am getting a system out of memory error, because each package is caching a lot of data with look ups.  So when I switch them to run out of process, the error goes away but everything is running in 32-bit.  Is there a way to run in 64-bit out of process?

October 31, 2007 9:11 PM
 

Andrew said:

Just been fighting with permissions - parent calls 3 child packages (due primarily to MS bloatware causing BIDS to take over 5 mins to load the entire package but also due to using wizards to create the bulk of the table imports) and since I'd had issues with "timeout waiting for memory" errors I tried out-of-process children.

Put the packages inside MSSQL and executed fine as myself (unfortunately a local WinAdmin).

Tried running as a proxy (now which twit decided to not actually validate a user & password against the domain controller when setting up credentials! :-( ) and only the parent package would execute (failed to load child packager error).

Finally thought to change the children to in-process and it all works again!

Now you'd think any child process would be created in the same security context as the parent...any idea which it is created in (I'd assume NOT localsystem since that is a local admin, hence DB sysadmin, hence should be able to load the package?)?

December 12, 2007 12:46 AM
 

fordareh said:

I'm actually having a problem where I have a Parent package with six Execute Package tasks - all set to ExecuteOutOfProcess: True.  And they need to be that way, executing them simultaneously would cause a bug in 3 of them.  I really am "simply using many packages to structure [my] solution in a more modular fashion".

Only trouble is, I'm getting a User:Diagnostic warning with the text, "Based on the system configuration, the maximum concurrent executables are set to 4".  It's not so much trouble, because my packages don't *seem* to be running simultaneously.  However, I'm afraid this may just be one of those hidden errors in parrallelism just waiting to break my package.

Here are the full details of the message, any thoughts?

Diagnostic,HOT-DAMN,HOT-DAMN\Joshua A. Stevenson,Package,{0BB79331-A7CF-4B0C-A49E-3EE9F3C0349C},{9130502E-23BA-4D54-B875-DDCA0CAC144E},4/14/2008 11:10:01 AM,4/14/2008 11:10:01 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 4.

User:Diagnostic,HOT-DAMN,HOT-DAMN\Joshua A. Stevenson,Package,{E9AC0636-2D97-4D53-A042-D27CC8FADCD8},{9130502E-23BA-4D54-B875-DDCA0CAC144E},4/14/2008 11:10:08 AM,4/14/2008 11:10:08 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 4.

April 15, 2008 4:00 AM
 

fordareh said:

I'm so sorry ... the are absolutely NOT set to ExecuteOutOfProcess: True.  All of them are set to ExecuteOutOfProcess: False.

...which I'm reading as, "Execute in Process"

April 15, 2008 4:02 AM
 

jamie.thomson said:

fordareh,

Don't worry about it. There is a setting called MaxConcurrentExecutables and that settng is conflicting with what ou are trying to do which is (seemingly) attempting to run 6 executables simultaneously.

its not a problem.

-Jamie

April 17, 2008 7:51 AM
 

Sudhir said:

I have 12 child packages within a parent package and I want them to execute simultaneously. So I set MaxConcurrentExecutables to 4. Some of the child packages fail to execute, and this incident occurs sporadically. And it is not necessarily the same child packages that fail to execute. I have the property ExecuteOutOfProcess=False. I also configure the variables in the child package to configure from the parent package.

There is also a task in all the Child Packages where I modify the variables in the child packages. So whenever the child packages fail, they always do at this task, giving me an "Object Reference not set to an instance of Object".

Any thoughts? Am I missing something here?

Thanks.

April 21, 2008 10:48 PM
 

Ian said:

Hello,

I am having a weird issue where randomly the parent package will fail on a execute child package step with the error "cannot access file, locked by another application or process" even though the child packages are not locked.  If I rerun the package there is a 50/50 chance that it will succeed.  This only happens within BIDS.

Everything is executing in-process and MaxConcurrentExecutables = -1

Thanks,

Ian

www.sqlblog.ibeckett.com

May 1, 2008 3:15 PM
 

jamie.thomson said:

Ian,

This is a pure guess.

Packages in BIDS are executed by a process called DtsDebughost.exe. Child packages called from there are executed by dtshost.exe. These are managed processes and can sometimes be left as running processes (viewable in Task Manager) after the packages have actually completed. I'm guessing this is because they are waiting for garbage collection.

Anyway, I am guessing these might be the problem. You could kill the processes manually if they are causing a problem.

-Jamie

May 1, 2008 4:18 PM
 

Ian said:

I think you might be right. I notice these stranded processes in my task menu when debugging all the time and have a tendancy to kill them - good to have validation that I'm doing the right thing... guess I just need to stay on top of it.

Thanks,

Ian

May 2, 2008 3:58 AM
 

Ian Beckett said:

be sure to kill rogue processes when debugging SSIS packages in BIDS 2005

May 2, 2008 4:08 AM
 

fordareh said:

Jamie -

Sorry it's been so long since I've been back.  I *don't* want my packages to run simultaneously.  In fact, that would be problematic.  All my packages are set to 'ExecuteOutOfProcess: False', though.  Why would I be getting warnings about concurrent executables if they are executing in process?

I have 8 packages that are supposed to run one after another using ExecutePackage tasks from an separate 'master' package.

May 2, 2008 4:28 PM
 

jamie.thomson said:

fordareh,

I presume you have precedence constraints between your execute package tasks, right?

-Jamie

May 2, 2008 5:18 PM
 

fordareh said:

:-) ... I didn't realize those arrows had such a official name.  

At any rate, I do have precedence constraints dictating the order of execution in the master task that contains all the execute package tasks.  

The strange thing is that the tasks are definitely running in order.  They all rely on the same underlying SQL table, so if they were executing simultaneously, my change logs would look disastrous. Plus, this is a warning, not an error.  It just bugs me, because at the end of an execution, I would like to look at the run's file and see nothing but a Package Start and Package End message.

May 5, 2008 5:24 PM
 

jamie.thomson said:

fordareh,

My best guess would be that you have some executables witin one of your child packages running concurrently. of course, I can't really say because I'm not there :)

Like I said before, I don't think its a problem.

-Jamie

May 6, 2008 10:03 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems