Refresh tables across environments using SSIS

[swathi rekha]

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:

1. Tables - dbo.A and dbo.B
2. Database - myDB
3. Servers - SrvD [Dev] and SrvU [UAT]
4. Service Account - refresh
5. 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.