You could be dealing with a delegation/impersonation problem as DimUser suggested.
If your SSIS is just fetching data from one DB server and delivers it to the other the solution is much easier and should be to set up an SQL Server Agent Proxy.
If you are executing the SSIS Package from Visual Studio or SSMS by hand the package will try your Windows credentials to log on to the SQL Server. If your account has the correct rights it will succeed.
If you set up an SQL Server Agent Job to execute that package the service account running SQL Server Agent executes this package. He does not have your Windows credentials so he will try to use anonymous login resulting in this error message.
To enable SQL Server Agent to execute a package using a different account you have to set up three things:
- Create a credential
- Set up a proxy account
- Configure the SQL Server Agent step to use the proxy account
Create a credential
Connect to the SQL Server that should execute your package. Right-click Security and select "New Credential...". Enter a descriptive name (Credential name), select the Windows account you intend to use (Identity) and enter the password. Click OK.
For initial testing, you can use your own Windows account. For production use, I would suggest creating a dedicated AD Service Account (with minimal permissions).
Set up a proxy account
Expand the SQL Server Agent folder. Open Proxies and right click on "SSIS Package Execution" selecting "New Proxy...". Enter a descriptive proxy name and use the credential you created earlier.
Configure the SQL Server Agent step to use the proxy account
Open your Agent Job, select the properties of your step executing the SSIS Package. Now you can select your proxy account in the "Run as:" drop-down list.
If your package is deployed to the SSIS Catalog you need to grant the Windows login you used for the credential the "SSIS_Admin" role on SSISDB as well.
For that, you need to create the account as a regular Windows login in SQL Server (Public) and map the user to SSISDB using the SSIS_Admin role.