I am trying to refresh two tables from a higher environment (UAT) to development. Both environments reside on different servers. As the refresh is to be done every month, I am automating the process. The SSIS package fails despite all permissions.
Background:
- Tables -
dbo.A and dbo.B - Database -
myDB - Servers -
SrvD [Dev] and SrvU [UAT] - Service Account - refresh
- SQL Agent Proxy account for SSIS package run is available on SrvD.
I have written a simple SSIS package containing two sequence containers each for a table. Each container contains a truncate, DFT to copy data, SQL Task to log the refresh activity. Once entire process is complete, send out an email to the downstream system. I have scheduled this package from a SQL Agent job.
