April 6, 2015 at 7:23 am
I put this hear because there is no BI forum for SQL 2008. Site admins, please take note and consider have a uniform set of forums for all versions of SQL Server, where a given feature exists.
Anyway, We have a job that has been running on SQL 2008 since September of 2011 successfully. Over the weekend it began failing for the following reason:
Started: 5:05:01 PM Error: 2015-04-05 17:05:02.47 Code: 0xC00220E8 Source: EXEC - Run Package Description: Error 0x80070003. Unable to access the package file, "C:\Projects\PIP\PaymentContingency\PaymentRequestExport\ParticipantsToPayExport.dtsx". Make sure the file exists and you have permission to access it. End Error Error: 2015-04-05 17:05:02.47 Code: 0xC00220DE Source: EXEC - Run Package Description: Error 0x80070003 while loading package file "C:\Projects\PIP\PaymentContingency\PaymentRequestExport\ParticipantsToPayExport.dtsx". The system cannot find the path specified. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:01 PM Finished: 5:05:02 PM Elapsed: 1.373 seconds. The package execution failed. The step failed.
NOTE: We have two Agent jobs that use the same package, with different configs. Both began failing at the same time. Log info you see may be from either job, so diregard the job names.
From what I see, the dtexec.exe is looking at the package and trying to access the development path for the PACKAGE. We aren't even talking about it trying to access a different config file, but a different location for the package.
I have tried executing from a command line, copying the exact command line from the job, and it works just fine there.
C:\Users\CStamey>dtexec /FILE "I:\InstalledSSIS\PIP\PaymentContingency\Export\Pa
ymentFileProcessor.dtsx" /CONFIGFILE "I:\InstalledSSIS\PIP\PaymentContingency\E
xport\StatePayForm_EXPORT_PROD_COTS.dtsConfig" /CHECKPOINTING OFF /REPORTING E
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 7:33:38 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 7:33:38 AM
Finished: 7:33:38 AM
Elapsed: 0.39 seconds
It only fails when executed by SQL Agent. I have scripted and deleted the job then recreated it from script, even though there was no change we could see in the job, since 2011. No help.
I redeployed the package from the original source in our archives. No help.
My colleague has done:
1)Package appears valid, but is set up in a way that makes it difficult to troubleshoot, without actually invoking in the package; I hesitate to do that in a production environment.
2)Monitoring the system during execution does not report any significant error
3)Nothing significant in the SQL logs, except a note that the job failed
4)Nothing significant in the SQL Agent logs, except a note that the job failed
5)Packages do not show as having changed
6)Configs do not show as having changed
7)No significant errors in the application event log (aside from a report of package failure)
8)No significant errors in the security log
9)No significant errors in the system event log
10)SQLService is a local administrator on the system
I am to the point of thinking there is a corrupt dtexec.exe or supporting DLL, but want to get your input first.
My logs from the other night:
Date4/5/2015 4:01:00 AM
LogJob History (PIP Request Approval to Pay Export)
Step ID1
ServerTARHMSSQL62\SLR_PROD_COTS
Job NamePIP Request Approval to Pay Export
Step NameExecute PIPSLR Payment Contingency EXPORT Package
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: HISNT\sqlservice. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:01:00 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 4:01:00 AM Finished: 4:01:01 AM Elapsed: 0.452 seconds. The package executed successfully. The step succeeded.
Then, on the next run:
Date4/5/2015 4:16:00 AM
LogJob History (PIP Request Approval to Pay Export)
Step ID1
ServerTARHMSSQL62\SLR_PROD_COTS
Job NamePIP Request Approval to Pay Export
Step NameExecute PIPSLR Payment Contingency EXPORT Package
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: HISNT\sqlservice. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:16:00 AM Error: 2015-04-05 04:16:01.68 Code: 0xC00220E8 Source: EXEC - Run Package Description: Error 0x80070003. Unable to access the package file, "C:\Projects\PIP\PaymentContingency\PaymentRequestExport\ParticipantsToPayExport.dtsx". Make sure the file exists and you have permission to access it. End Error Error: 2015-04-05 04:16:01.68 Code: 0xC00220DE Source: EXEC - Run Package Description: Error 0x80070003 while loading package file "C:\Projects\PIP\PaymentContingency\PaymentRequestExport\ParticipantsToPayExport.dtsx". The system cannot find the path specified. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:16:00 AM Finished: 4:16:01 AM Elapsed: 1.061 seconds. The package execution failed. The step failed.
I am stumped.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 7, 2015 at 6:09 am
A reboot overnight seems to have cured the problem.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply