SSIS – ExecutePackage

On my current project, we’re using SSIS quite extensively for uploading data (text files) from the mainframe to our database. Although the body of the work is mostly finished, I still had to bundle all the work in an easy to run package. Until now, I had always manually run all the different packages that are required for the upload. So last week, I spent some time creating a ‘master’ package that calls all the (child) packages in the correct order. I first edited all my child packages to use parent configuration: this way, I can specify the configuration at the top-level, and all child-packages pick up this setting automatically (and I still can run the packages by themselves if I want). I then created the parent package: it consists of a number of ExecutePackage tasks, which just pass control the configured child package. I thought that all of this could be completed in a few hours, but alas, when I tried to run the parent package, I almost immediately got the following error:

I hoped the child package’s output would show more information on what went wrong, but nope, nothing. After some experiments, I found out that I could get things to work by setting the ExecuteOutOfProcess property of the ExecutePackageTask to true. This forces SSIS to create a separate process for each child package to run in. However I noticed that these processes are not cleaned up quickly: several minutes after the parent package had completed, I still saw several dthost.exe process running, each taking a considerable amount of memory. Btw, I half expected this to be the case, as I remembered this post.

So, although I now had a work-around, I still was confused as to why my packages wouldn’t work if I ran them in the same process as their parent. I didn’t want to spend more time on it, so I went to create some other parent packages (for other parts of the project). I tried running one before I had set the ExecuteOutOfProcess property of each child-package, and behold, some (but not all) of the child-packages did run! I compared the properties of the child packages (both of the ExecutePackage task as of the child package itself), and found that the only difference between a working and a non-working child package was that the DelayValidation property of the non-working ones was set to true! Changing it to false solved the problem!

So, the short version of this is: if you get the above error when running a child-package with the ExecutePackage task, make sure that DelayValidation is set to false on the child-package definition (the package itself, not the ExecutePackageTask)!

Like this:

LikeLoading...

Related

18 Responses to “SSIS – ExecutePackage”

Steve,
Can you explain how you can still run the package by itself when pointing the configuration to use the Parent variable?
I have a parent package that calls other file system packages and would like to pass the dtsConfig file/location to the child packages via Execute Package task. SSIS rejects and ingores anything that I append/concatenate to my dynamic expression for PackageToRun. I would like the child pkg to be independent (run by itself) as well as being called by a parent.
Thanks
Anatole

In the MasterPackage set the DelayValidation task to False for the spesific Execute Package Task. The child package should have its DelayValidation task set to True. If you have tasks within the child package that require a delayed validation, these can safely be set to DelayValidation=True.

I am same issue of running package within a package. I get validation error. I do have my child package delayvalidation = false. Few of my packages run while few give me delayvalidation error. Even thou all are setup same. Any idea what can be wrong.