I have a single SSIS package that executes 10 different SSIS packages. All
are stored in the sql server package store.

When I execute the outer package manually, it completes successfully. If I
execute it from a sql agent job, it fails. Also, if i change the job to
point to each individual ssis package, each run successfully, i just cannot
point the sql job to a package containing child packages. The error returned
in the logs is:

-1073602332,0x,Error 0xC0012050 while preparing to load the package. Package
failed validation from the ExecutePackage task. The package cannot run.

Thinking it was security, i changed the agent to run as an admin user with
full rights to every database, but no luck -- so security is not the problem.

Strange points to consider:
1) Everything works fine in WindowsXP with SQLServer 2005, but only fails
in Server2003 with Sqlserver2005
2) Not every child package fails. Only two fail, but are using the same
connections as the other packages.
3) All packages run fine individually, and the parent package runs fine
manually.

Hi John,

It's likely that you have incompatible security settings between the parent
package and child packages. The key point is that sensitive information in
the child package, like passwords, gets saved in the parent package. As an
example, if you have set ProtectionLevel property to
EncryptSensitiveWithPassword in the child package, then use
DoNotSaveSensitive in the parent package, the parent package will not have
the password available when it tries to open the child package. Execution
will therefore fail.

Also, if you are using the EncryptSensitiveWithUserKey only the account and
password that created the package can execute it. If you use SQL Server Agent
to execute the package, it wont run because it is trying to use a different
user key to open the package. Try using EncryptSensitiveWithPassword on all
the packages consistently. Be wary about using EncryptAllWithPassword because
if you lose the password, your whole package is lost for ever.

I suggest you look at the settings of the ProtectionLevel property of your
parent and child packages, and make sure they are set in a way that is
compatible.

Charles Kangai, MCT, MCDBA

"John Collins" wrote: