How do I troubleshoot SSIS packages failed execution in SQL Agent job?
When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:
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.
The following 4 issues are common encountered in the SSIS forum.
1. The package's Protection Level is set to EncryptSensitiveWithUserKey but your SQL Server Agent service account is different from the SSIS package creator.
2. Data source connection issue.
3. File or registry access permission issue.
4. No 64-bit driver issue.
Package Protection Level issue:
For the 1st issue, you can follow the following steps to troubleshoot this 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.
A common solution to this issue is that you create a proxy account for SSIS in SQL Server Agent and then specify the proxy account as the "Run as" account in the job step. The proxy account must be the same as the SSIS package creator.
You can also change the SSIS package protection level to EncryptSensitiveWithPassword and specify the password in the command line in the job step.
Data Source Connection Issue:
It happens when you are using Windows Authentication for your data source. In this case, you need to make sure that the "SQL Server Agent Services" service account or your Proxy account has the permission to access your database.
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.
The following 4 issues are common encountered in the SSIS forum.
1. The package's Protection Level is set to EncryptSensitiveWithUserKey but your SQL Server Agent service account is different from the SSIS package creator.
2. Data source connection issue.
3. File or registry access permission issue.
4. No 64-bit driver issue.
Package Protection Level issue:
For the 1st issue, you can follow the following steps to troubleshoot this 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.
A common solution to this issue is that you create a proxy account for SSIS in SQL Server Agent and then specify the proxy account as the "Run as" account in the job step. The proxy account must be the same as the SSIS package creator.
You can also change the SSIS package protection level to EncryptSensitiveWithPassword and specify the password in the command line in the job step.
Data Source Connection Issue:
It happens when you are using Windows Authentication for your data source. In this case, you need to make sure that the "SQL Server Agent Services" service account or your Proxy account has the permission to access your database.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.