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: setting child variables

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

fleo wrote:

Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.

How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

Thanks

No, it's not possible using the Execute Package Task. However, using Parent Package Configurations, you can hook the parent variable to a child variable.|||

fleo wrote:

What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET \Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?

You could use execute process task to run the child packages via dtexec and using the /SET option for pushing the parameter. The only problem I see with that is that the master package will not know when the package finish as opposed to Execute package task.

Just an idea you do the test.

|||

We have a "common" stored proc to run SSIS packages (dtexec is called by that sp).
It can be used in a Execute SQL task and we are able to determine when the execution is done and wheter the package was executed successfully. But building the command and retrieving the result is quite a hassle...

The parent/child package configuration will do the trick. The only thing I dislike is the parent being dependent upon the child (maybe I'm from another school of thought).

Thanks all for your input.

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.

Execute Package Task in a loop works, but...

Okay, can someone explain why when I execute children packages within a loop that only a couple of them work?

When I first started this I had Child Package 1 and Child Packge 2 working from the Parent Package 1. It would loop through twice (I had 2 organizations in my outer loop) and pass in the Parent Package variables correctly.

I am using a simple "Foreach Item Enumerator" with a collection of string enumerators that are the Names of the children packages - these are assigned to a variable that is scoped to the outer loop.

Now, when I add a new Child Package 3 and set it up the same as the other 2, when it goes to execute Child Package 3 it fails with the error:

"Error: The connection manager "[My Package Name].dtsx" is not found. A component failed to find the connection manager in the Connections collection. "

What in the world have I done? I was under the assumption that since the first 2 packages worked, that any other packages I added would work the same.

I answered my own quesiton on this..

It seems as though when I created the first 2 children packages, I also created the "File" connection for them.

YOU MUST create the "File Connection" for each child SSIS Package you plan to execute.

If anyone knows different, please let me know.

|||

ronemac wrote:

I answered my own quesiton on this..

It seems as though when I created the first 2 children packages, I also created the "File" connection for them.

YOU MUST create the "File Connection" for each child SSIS Package you plan to execute.

If anyone knows different, please let me know.

Wrong I think. You only need one connection manager and you should use expressions to dynamically change the ConnectionString property of that connection manager on each loop iteration.

Regards

-Jamie

execute package task can't continue when the child package failed?

I use a execute package task to run a child package in which I run some sql task.

as the error handle I insert a script task and link a line from execute package task to script task

of course the line is red,

but I found when the child package failed, the execute package task turns red,it stopped

the script task can't be run, I don't konw why?

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

|||

ycjj wrote:

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

i'm not sure if this will work. however, try setting the FailParentOnFailure property of the child package to "false".|||

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

|||

ycjj wrote:

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

ok. the child package will always stop executing when it fails. my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.|||

>my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.

first thanks for your attention.

yes, I want to return to parent package after the child package failed and I hope that the parent package will continue to execute from the execute package task on the failure precedence constraint to a script task as a error handle.but now the execute package task stops if the child package fails.

maybe the failure precedence constraint is wrong......

|||

I found the prpperty "LogicalAnd" of the failure precedence constraint from execute package task to script task(error handle)

I change the value of property "LogicalAnd" from "True" to "False", it works well, but I still don't know the reason.

|||

I made some study and know the reason now .

in my parent package, some execute package tasks have a common error handle task, but I set property "LogicalAnd" of these failure precedence constraint "false"(dedault), it means all of execute package tasks fail will cause error handle task running, of course this is not right,

it must be one of of execute package tasks fails will cause error handle task running, so the value of property "LogicalAnd" is "True" is correctly.

thanks.

Execute Package Task always fails.

I have a package (i.e. child package) which runs itself perfectly fine without displaying any error.

However when I embeded this package inside another package (i.e. parent package) using Execute Package Task. The task always fails. It seems strange enough.

The child package has two variables that need to be passed in from parent package.

Can any expert here please help out? Thanks.

- Steve

What are the errors? Look in the output window and turn on some logging.|||

Error 1 Error loading Scheduling_F580021.dtsx: The connection "{34589715-3053-4b26-9769-0b8ecc198d85}" is not found. This error is thrown by Connections collection when the specific connection element is not found. C:\Scheduling_data.dtsx 1 1
here is the error:

I have kept getting this annoying error.

|||

Just doulble check the connection manager that is being used by the Execute PAckage task. I would drop the connection mamanger and add it back from the Execute Package task: COnnection : <New Connection>.

Rafael Salas

|||

Here is what I did to fix it ...

i delete the troubled child package completely, and rewrote it from the sratch. And it started to work.

Just as a reminder:

Please note that the old trouble child package contains some copy-and-pasted tasks (and connections) from parent package. I often use copy-and-paste to develop packages. Seems that there are some problem with it. Not sure if SSIS has a bug or something else.

|||

The problem looks like it was with a task which had been pasted in. Most tasks actually store the connection ID, the "{34589715-3053-4b26-9769-0b8ecc198d85}" GUID, rather than the name, although you generally always see the name. If you see the GUID it normally means that the connection with that ID does not exist, and therefore it cannot translate the ID to the name for display and general use or validation. In otherwords the task refers to a connection that does not exist.

Using the ID over the name means that you can rename connections and stuff keeps working, but conversely means that you cannot just create a connecion with a matching name, and expect it to be the same.

|||

you made a good point.

It should be more cautious to copy-and-paste tasks, connection, or packages for rapid SSIS development. A package works perfectly fine using copy-and-paste task/connection if it runs itself.

This does cause a pain when developing a complex package (parent-child package) if we cannot use copy-and-paste. Is Microsoft SSIS team aware of it or do they have any plan to fix it ?

|||

Personally, I do not see that such pain you are taking about. If you are copying a DataFlow task from one package to another; you would need eventually to check or re-define your connection managers; since you may need the same combination of tasks but different connections. Changing the connection managers inside of a task is just a click and select from a dropdown list task.

In the other hand, if you need more than copying the dataFlow task, meaning you also need the references to the connection managers; then you are better creating a copy of the whole package and then adding and droping tasks/logic as needed

This is just my opinion...

Rafael Salas

|||

I not only copy-paste tasks from parent package to child packages, I copy-paste all connections as well. I used the same connections which configures on the fly based on the ConnectionString. The connectionString is a variable which is assembled and determined at the runtime.

I also copy packages too all times. I use copy-paste on everything for rapid development. Somewhere, the strange errors come up when I tried to build a complex package (like grand-parent package). And it is very hard to debug. I know and have used debugging tools and logging as well. It can be painful without knowing the code behind the scene. I run all of packages with settings (i.e. connections, queries, and tasks) to be configured on the fly.

|||

Perhaps you should look at making some packages re-usable if you do so much copy and paste. It is often easier said than done, but since you say you do it so much and you find this such an issue I'd be surprised if you could not get some reuse.

The benefit of the current behaviour is the ability to rename connections without breaking task references, something that got a lot of feedback during the beta, and for me that is very usefull. Make a suggestion though - http://connect.microsoft.com

|||

As a matter of fact, I have exploited a lot of reuse feature as much as I can.

Here is the scenario: Say, I need to develop 300 packages. I do not want to create a blank package to start with. Often times, I use Copy-and-paste some similar package and rename it. That could save me a lot of time. If I start with an existing package, it may save me a lot of time.

Steve

execute package task

I am missing something simple that's killing me.

I am trying to execute a child ssis package using the execute package task. I add execute package task to the parent package and in the package task editor, under the package options I choose: filesystem for location, I choose new connection and create a connection to the package on the filesystem I want to execute, packagenamereadonly is blank. I execute the parent package and get an error that I did not specify the name of the package in the execute package task object. Now, since I can't populate the package name, as that option is readonly, I am not sure how to remove my headache. Anyone see what I'm doing wrong? None of these packages have security.

-Aaron

Ok, nevermind.

The error was actually in my child package. The child SSIS package was running a DTS 2000 package via the execute DTS2000 package task. The DTS 2000 package was loaded internally and for some reason there was a corruption somewhere in that task. When I deleted the task and reloaded the DTS 2000 package the problem was resolved.