Package that calls child packages fails when run as a job

Question

Post reply

Package that calls child packages fails when run as a job

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 8, 2014 at 1:20 am

#289765

I have a Package that calls child packages fails when run as a job.
It runs fine interactively but when I run it as a job I get the following error:
Date09/08/2014 03:02:38 AM
LogJob History (Load_STG_Data_Policy_Chilld)
Step ID1
ServerCYP_BI
Job NameLoad_STG_Data_Policy_Chilld
Step NameLoad_STG_Data_Policy
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: CYPRESSIG\SQLServerAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 03:02:38 AM Error: 2014-09-08 03:02:39.39 Code: 0xC00220EC Source: Execute Dependent Child Packages Description: Error 0x80070005. Failed to create an instance of empty child package. The DCOM configuration or SQL Server Integration Services installation on your machine might be corrupted. End Error Error: 2014-09-08 03:02:39.39 Code: 0xC00220E4 Source: Execute Dependent Child Packages Description: Error 0x80070005 while preparing to load the package. Access is denied. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 03:02:38 AM Finished: 03:02:39 AM Elapsed: 0.951 seconds. The package execution failed. The step failed.
Any help would be greatly appreciated.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

The error is quite clear.

Access is denied

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Koen Verbeeck (9/8/2014)
The error is quite clear.
Access is denied

But why am I getting the error?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Welsh Corgi (9/8/2014)
Koen Verbeeck (9/8/2014)
The error is quite clear.
Access is denied
But why am I getting the error?

Because something, somewhere in the package requires permissions which the SQL Agent Service account (the one mentioned after "Executed as user:" in your initial post) does not have.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 4

Have you tried running each child package in a separate job? That may help you pinpoint the problem.

Regards

Lempster