SSIS Package migration from 2016 to 2019 w/TDE database

Question

Have a ssis package that is executed by a SQL Job and it gives the following error:

Date 1/14/2021 7:47:55 AM
Log Job History (REDCAP data pull test - 1 job)

Step ID 1
Server HWDWWARE1\DW2016
Job Name REDCAP data pull test - 1 job
Step Name redcap data pull
Duration 00:00:04
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Microsoft (R) SQL Server Execute Package Utility
Version 15.0.2000.5 for 32-bit
Copyright (C) 2019 Microsoft. All rights reserved.

Started: 7:47:59 AM
Failed to execute IS server package because of error 0x80131904. Server: hwdwware1\dw2016, Package path: \SSISDB\REDCap\RedCapDataPull\Package.dtsx, Environment reference Id: NULL.
Description: An error occurred during decryption.
Source: .Net SqlClient Data Provider
Started: 7:47:59 AM
Finished: 7:47:59 AM
Elapsed: 0.203 seconds

Database has TDE enabled on new server.

Answer 1

Hi @WLugaila ,

1.Please check if the user who will run ssis package in sql agent job has permission to access to the SSIS Catalog database.
We can refer to Integration Services Roles (SSIS Service).

2.Please check if you set protection levels in ssis packages.
Encryption

Encryption, as used by package protection levels, is performed by using the Microsoft Data Protection API (DPAPI), which is part of the Cryptography API (CryptoAPI).
The package protection levels that encrypt packages by using passwords require that you provide a password also. If you change the protection level from a level that does not use a password to one that does, you will be prompted for a password.

We can refer to Access Control for Sensitive Data in Packages.

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.