SSIS package failed to run

Question

Fail to run the package from agent job. It said unexpected error. But when i run it in VS project it works.

Answer 1

Hi @Sin X,

To resolve the issue, use following methods:

Method 1: Use a SQL Server Agent proxy account. Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage. Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows for access control through SQL Server database roles.

Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.

Method 4: Use SSIS Package configuration files. Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.

• Method 5: Create a package template. For a long-term resolution, create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

See SSIS package does not run when called from a SQL Server Agent job step

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Sin X,

For troubleshooting reasons, you can try to connect to the SSIS run-time server via Remote Desktop (RDP), and try to execute SSIS package directly in the SSIS Catalog without SQL Server Agent Job.

If it is working, it means the issue is with the SQL Server Agent Job or its account.