SSIS Parent package calling Child package with ForEach Loop problem

Question

SSIS Parent package calling Child package with ForEach Loop problem

Lewis Dowson

SSC Eights!

Points: 988
More actions
May 10, 2010 at 7:56 am

#140260

Hi All,
I am having a problem calling a child package from a parent package using a loop.
The aim is to receive a list package names that need to be executed from a table in the database and then execute these packages using a ForEach loop which contains a Execute Package Task (the packages are deployed on SQL Server not the file system).
I've set up an Object variable to hold the record set and a String variable to hold the name of the package in each loop. Both are set up correctly and the Loop does change the variables in each itteration.
The problem occurs when I introduce the variable name as an expression into the Execute Package Task. If I explicitly tell the Execute task what pacakge to run then there is no problem, however I need to pass in the variable as an expression so that the package execution changes in each loop.
The error message I get is:
"Error: 0xC00220E4 at Execute Package Task: Error 0xC001000A while preparing to load the package. The specified package could not be loaded from the SQL Server database."
Any help would be greatly welcome
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

Try setting the DelayValidation property of the Execute Package task to True.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lewis Dowson SSC Eights! Points: 988 More actions · Answer 2

Hi. I've tried this but still having the same problem, any more ideas anyone? Thanks

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

You could try setting the initial value of the string variable to be a valid package name (ie, in Package / Variables).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lewis Dowson SSC Eights! Points: 988 More actions · Answer 4

Yep tried that too.

The error only comes about the moment the variable is used in the expression. If I call the same package directly (i.e. no expression) there are no errors. I have debugged it an the variable value is valid.

I think the problem is more deep rooted, i.e. There is a property not set but I am cluching at straws now.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 5

OK, can you post your expression then?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lewis Dowson SSC Eights! Points: 988 More actions · Answer 6

Yep,

The Expression is set against the PackageName property:

@[User::strPackageName]

If I evaluate the expression I get the following text:

\Markets\Import_BMUunit_GCDC

I have tried coping and pasting the above string into the execute task explicitly and removing the expression, no problem with spelling or anything.

Thanks