SQL Server Agent Job suddenly Failing

Bobby P 221 Reputation points
2021-11-09T05:26:38.67+00:00

We have a SQL Server Agent Job that has been running for months.

Now all of a sudden it seems as though it is timing out. And it is inconsistent. Like two weeks ago, it failed. Last week it ran fine. Now it has failed again this week.

We don't believe any jobs were added that would result in contention for resources.

We have attached the Job Failure which is a SSIS Package.

If anyone has ANY insight into this, we'd greatly appreciate it.

And Thanks in advance for your review.

Message
Executed as user: WE\svcp_sql_pclddb03. Microsoft (R) SQL Server Execute Package Utility Version 12.0.6164.21 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 3:10:53 AM Error: 2021-11-06 03:10:53.20 Code: 0xC0016016
Source: Eagle Datapass AbbVieSynthroidDeliversPreEnrollmentFile
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information.
This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error
Error: 2021-11-06 03:10:53.22 Code: 0xC0016016
Source: Eagle Datapass AbbVieSynthroidDeliversPreEnrollmentFile
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information.
This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error
Error: 2021-11-06 03:21:43.52 Code: 0xC0202009
Source: Data Flow Task - Extract AbbVie-Synthroid Delivers-Pre-Enrollment File OLE DB Source - Datapass_AbbVie_SynthroidDelivers-Pre-Enrollment File [22]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "PAZDB01.DynamicsCRM". .".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14
Description: "The OLE DB provider "SQLNCLI11" for linked server "PAZDB01.DynamicsCRM" reported an error. Execution terminated by the provider because a resource limit was reached.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14
Description: "OLE DB provider "SQLNCLI11" for linked server "PAZDB01.DynamicsCRM" returned message "Query timeout expired".". End Error
Error: 2021-11-06 03:21:43.52 Code: 0xC004701A Source: Data Flow Task - Extract AbbVie-Synthroid Delivers-Pre-Enrollment File SSIS.Pipeline
Description: OLE DB Source - Datapass_AbbVie_SynthroidDelivers-Pre-Enrollment File failed the pre-execute phase and returned error code 0x80040E14. End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:10:53 AM Finished: 3:21:43 AM Elapsed: 650.734 seconds. The package execution failed. The step failed.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2021-11-09T07:03:07.523+00:00

    Hi @Bobby P ,

    Please try to set the Protection Level property as DontSaveSensitive.

    And then re-deploy the package and run it for a try.

    access-control-for-sensitive-data-in-packages

    Also here is a same issue for your reference.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments