I am trying to connect SSIS to an AWS RDS SQL Server instance. Whenever I try to run the packages through the SQL Server Agent, I encounter this error message:
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.
From what I can find online, the recommendation is to create a proxy account. This does not seem possible since I cannot create credentials in the AWS RDS instance.
Is what I am trying to do possible? If so, how do I configure the user/package to run correctly?
Best Answer
scrap SSIS, and use a procedural model to do your ETLs. - Stage data from file to table via "BULK INSERT" call from stored procedure - Use Stored procedures to transform, any step, outside of direct FTP call, can be done via a stored procedure, probably need xp_cmdshell active to file level access such as xp_fileexist, to get a file listing via DIR or other command, but it can be done. I scrapped SSIS long ago and created my own meta data driven, ETL procedures that use ONE STORED PROCEDURE for all file loads to staging table to dynamically build BULK INSERT and INSERT/SELECT via compare of header to table columns. Setup meta data model for file settings, for build of Bulk Insert parameters. Not sure if you still need a proxy (and credential) for file system level commands.