Share via

ssis error - Failed to decrypt protected XML node

nononame2021 256 Reputation points
2022-01-17T13:09:43.493+00:00

my ssis failed with the below error, but all the task has been processed from ssislog table, but I found that the excel can't be copy to destination while the task has processed. the ssis is scheduled to daily , anyone know why the ssis can execute without below error in the first 2 days but why it failed suddenly on the 3rd day?

SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B

may I know if above error will block the process ? seems I run ssis manually while it causes error when deployed to environment

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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2022-01-17T13:39:38.97+00:00

    Failed to decrypt protected XML node "DTS:Password"

    Sensitive data like a password are stored encryted, either by user key or password. To decrypt the value SSIS muste have the information.
    Which protection level have you set for the package?
    See Access Control for Sensitive Data in Packages

    0 comments
  2. nononame2021 256 Reputation points
    2022-01-17T13:55:20.163+00:00

    the ssis job is set as daily, the first 2 days don't have this error , it only occurred on the 3rd day, why?

    will this error block the execution, if yes, why the first 2 day can ran ssis successfully while it only failed on the 3rd day?

  3. nononame2021 256 Reputation points
    2022-01-17T15:36:37.013+00:00

    anyone know why the error trigger on the 3rd and it can run successfully on the first 2 day for the daily job and nothing to change during these 3 days.

    0 comments
  4. ZoeHui-MSFT 35,556 Reputation points
    2022-01-18T05:59:21.193+00:00

    Hi @nononame2021 ,

    Please check below:

    Package Protection Level issue

    1. Check what the Protection Level is in your SSIS package.
    2. If the Protection Level is set to EncryptSensitiveWithUserKey, check the Creator in your SSIS package and compare it with the SQL Server Agent Service account.
    3. If the Creator is different from the SQL Server Agent Service account, then the sensitive data of the SSIS package could not be correctly decrypted, which will lead to the failure.

    seems I run ssis manually while it causes error when deployed to environment

    Please check the account permissions:

    1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
    2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

    Details see here ssis-package-doesnt-run-when-called-job-step

    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