Dear Support,
I have spent the past few months working on this and I've tried everything that is recommended from internet resources and I am still unable to get the following issue resolved.
I need to schedule an SSIS package in Visual Studio (SSDT) 2017 to run and here's what I've done after months of working on this:
1) In SSMS, created an extract using the Data Export Wizard and saved the package to the File System on the local drive
2) In Logins, created login and granted the login sysadmin and public server role, granted db_owner database role, granted SqlAgentRole, SqlagentOperatorRole,SqlagentUserRole to this account
3) Created server credential for this login
4) Created a proxy account for this login
5) Created a server agent job with the owner being my login account (admin)
6) Created the step for this job > setup the type for SQL server integration services package > Run as the login proxy account > package source = file system > for execution options = 32-bit runtime checked
7) in SSDT created a project > open and added the SSIS package > change the Runtime64bit to False > encryption is set to user key
Tested the package in Visual Studio = Works fine > No errors
When execute the job at job step, it errors with:
".... Connection manager "SourceConnectionOLEDB"..... "Microsoft SQL Server Native Client 11.0".... "Login failed for user 'xxxxxxx" SSIS error Code DTS_E_CANNOTCQUIRECONNECTIONFROMCONNECTIONMANAGER..
I've deleted and recreated the account as well as the job many times and redo the SSIS packages without any luck.
Please assist as I am now against tight deadline and need to get this resolved ASAP. Thank you