Problem:

We have created an SSIS package to load the data from a database in one server to another server. When tested in the local machine, it executed and data has been transferred as expected. But when we tried executing it through SQL Server Agent, it was throwing an error.

Error message:

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

Started: 1:30:08 PM

Error: 2023-03-20 13:30:09.81
Code: 0xC0016016
Source: Package
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: 2023-03-20 13:30:10.98
Code: 0xC0202009
Source: Package Connection manager "DB1"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80040E4D. An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'user1'.".
End Error

Error: 2023-03-20 13:30:10.98
Code: 0xC020801C
Source: src db1 [2]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "db1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error

Error: 2023-03-20 13:30:10.98
Code: 0xC0047017
Source: table1 SSIS.Pipeline Description: table1 failed validation and returned error code 0xC020801C.
End Error

Error: 2023-03-20 13:30:10.98
Code: 0xC004700C
Source: table1 SSIS.Pipeline Description: One or more component failed validation.
End Error

Error: 2023-03-20 13:30:10.98
Code: 0xC0024107
Source: table1Description: There were errors during task validation.
End Error

DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:30:08 PM

Finished: 1:30:10 PM
Elapsed: 2.25 seconds

Root cause:

When an SSIS package is created, it is created with EncryptSensitiveWithUserKey protection level. So when you execute the package from the same machine where you created it, it will automatically decrypt the sensitive property like passwords. But when we try to execute it through a SQL agent job, it uses a different account than the one used in creating the package. The package will be loaded but the decryption of the sensitive property will not happen. So you get an error like the above as login failed or can not acquire connection manager.

Solution:

There are several ways to solve this problem. You can read the same from Microsoft documentation. This is how I solved the issue.

1. Change the ProtectionLevel property in SSIS from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword

2. Add a password to the package.

3. Use this password when configuring the SSIS package in the SQL agent step.

Thanks for reading. I hope you can solve this problem. If you still having an issue, please let me know in the comments.