SQLTeam.com | Weblogs | Forums

SSIS package not running through SQL Agent

Hello,

I am trying to import data from Universe Database from SQL using Import Export Wizard.
When I create the package and save it, it works fine, imports the data.
But when i try to schedule the package though SQL agent, it gives below error:

Description: ADO NET Source has failed to acquire the connection {2C2D1DE9-1FB9-4247-8196-F3796A807D82} with the following error message: "Could not create a managed connection manager.". End Error Error: 2017-12-04 14:54:43.01 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - Query failed validation and returned error code 0xC0208449. End Error Error: 2017-12-04 14:54:43.01 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-12-04 14:54:43.01 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 02:54:42 PM Finished: 02:54:43 PM Elapsed: 0.891 seconds. The package execution failed. The step failed.

I tried every possible online solution, but not that helpful.

Please guide me on this.

Thanks in advance.

What SQL Version? Project or Package Deployment? If Project, what's in the catalog config?

How are you authenticating.

Is this an issue of your SSIS package running as the local SQL Agent account which would not normally have the permissions on the other server?
It is often a good idea to run the task as a proxy which you could assign the permissions on both databases that are required to complete the job.