Experiencing connection issues when running SSIS package (via SQL Agent) on Azure VM with sql installed and the destination is the new SQL Azure DWH (PaaS)
We know:
- SSIS Package works on-prem to on-prem, on-prem to Azure SQL DWH
- Can connect to Azure SQL DWH from SQL VM with ODBC, SSMS
Problem
- To override the SQL Agent account running the SSIS Package in SQL Agent. We use the credential method, proxy, run as proxy - traditional method. However we can only connect to the Azure SQL DWH (PaaS) with local sql account and therefore unable to set this up as a credential on the SQL VM. Obviously without a means to authenticate it will fail
Any ideas?