Error when calling child package from scheduled parent
-
I have a puzzling problem: I have just deployed a set of packages to a new server. There is a parent package which calls a set of child packages using the execute package task. All the packages are stored in MSDB.
I have scheduled the parent package to run once a day. The package starts ok, but when it trys to execute one of the child packages (it doesn't matter which one, they all error) I get the following error message:
Code: 0xC00220E4 Source: MP S2F VisitSummary Description: Error 0xC0014062 while preparing to load the package. The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.). The SQL statement that was issued has failed. . End Error
When I run the parent package on my box (still pointing at the packages stored in the server's MSDB) it runs fine. If I schedule one of the child packages to run on its own, it executes correctly. So, it is just when a parent package tries to call a child package that I get the above error.
Anybody have any ideas?
Cheers,
Tom
Life: it twists and turns like a twisty turny thing
-
I don't use MSDB for storing packages so am shooting in the dark - does agent have access rights to MSDB?
What account is it running under, can you log on to machine with said account and access the packages through SSMS?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I don't have access rights to the server to be able to log on, but using the agent I can schedule the child packages to run without any problems, so the agent has all the correct access rights.
Life: it twists and turns like a twisty turny thing
-
discovered the problem: even though the MSDB connection was dynamic and pointing to the correct server, the packages were holding on to old connection details. I discovered this when looking through the data sources tab on the job step.
Therefore, the account was trying to access the MSDB on a different server to which it didn't have access. I rebuilt the packages, redeployed them and hurray! they worked.
Life: it twists and turns like a twisty turny thing
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply