SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

When you call the SSIS package from a SQL Server Agent job step, the SSIS package does not run. However, if you do not modify the SSIS package, it will run successfully outside SQL Server Agent.

It works if the package is created in the same environment with the same credential and scheduled it in a job with the same credential with a proxy account, but when working with multiple servers with different credential, some issues may arise.

For example:

• When the package developer use BIDS to create and save the package and another developer tries to run the package, it will throw an error “Login Failed for the user “
• If the package developer deploys the package to the production and when schedule it to execute through SQL server agent job, the job will fail.

Reason:

• The current user cannot decrypt secrets from the package. This scenario can occur if the current account or the execution account differs from the original package author, and the package's ProtectionLevel property setting does not let the current user decrypt secrets in the package.
• A SQL Server connection that uses integrated security fails because the current user does not have the required permissions.
• File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses.

Resolution:

Use a SQL Server proxy account:  The Proxy account must use credential that allows SQL server agent run the job as the account that has the required permissions. This method decrypts secrets and satisfies the key requirement by the user.

But the same will not work if we move the package to different computer even if we use the correct proxy account.

Set the SSIS Package Protection Level property to ServerStorage: Change the SSIS Package Protection Level property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles. The one major drawback is that it will not work when deploying a package from a development environment in BIDS to another environment.

Use SSIS Package configuration files Use config file to store sensitive information, and then store this 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 in a job, the required information is loaded from the configuration file.  Ensure that the configuration files are protected as if they contain sensitive information. The disadvantage here is to maintain a configuration file in a secured folder and there may be chances of viewing the config file or getting login failed issue again.

So the best option that works well in development environment where several developers may want to edit the same package is to create the package with the connection manager on windows authentication account with least privilege or create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

Permission to execute the package:

db_ssisadmin	Enumerate own packages.	Import packages.
or	Enumerate all packages.	Delete own packages.
sysadmin	View own packages.	Delete all packages.
	View all packages.	Change own package roles.
	Execute own packages.	Change all package roles.
	Execute all packages.	Bitmap
	Export own packages.
	Export all packages.
	Execute all packages in SQL Server Agent.
db_ssisltduser	Enumerate own packages.	Import packages.
	Enumerate all packages.	Delete own packages.
	View own packages.	Change own package roles.
	Execute own packages.
	Export own packages.
db_ssisoperator	Enumerate all packages.	None
	View all packages.
	Execute all packages.
	Export all packages.
	Execute all packages in SQL Server Agent.
Windows administrators	View execution details of all running packages.	Stop all currently running packages