1- SSIS package is using AWS Redshift ODBC driver (32Bit)/Data source ("Redshift") to execute sql statements.
2- I can run the package from the studio or job agent manually without any issue but when the sql job agent tries to run job at scheduled time, it is failing and giving following error:
"Error code: -1073573396Failed to acquire connection "Redshift". Connection may not be configured correctly or you may not have the right permissions on this connection. "
I have checked "32 bit" execution of SSIS package under configuration in job agaent.
3- I have tried using different ways to resolve the issue but getting same error.
- Run SSIS package using proxy account to execute under job agent
- Created XML configuration and stored Redshift credentials there and changed SSIS protection level to "Donot Store sensitive".
- Ran the package from .bat file by calling dtexec utility and executing SSIS package directly
I have created both User DSN and System DSN and tried with both data sources but getting same error.
Sql server env info: Microsoft SQL Server 2012 (SP4) (64-bit)
Any help would be appreciated!