Call SSIS Package on 2008 server from SQL Agent Jo

Question

I'm trying, unsuccessfully, to remotely execute an SSIS package. The package resides on a SQL Server 2008 instance and I'd like to call it from a Job on a 2005 server. The error I'm getting is:

The package could not be loaded. The step failed.

If I go from 2008 to 2008, there is no error. Any ideas?

Answer 1

Does the account your are running under have permissions on the SQL 2005 box. You can create a proxy under which to run the job.

Answer 2

Assuming that you're attempting to execute the package with a dtexec command, the issue is that the package is executed on the calling server, not the server where it is stored.

Since the 2005 SSIS service can't execute 2008 packages, the step fails.

You need to trigger the dtexec on the server where you want the job to run. The easiest way to do this is probably to set up a SQL Agent job on the 2008 server which starts the package, and trigger that from the 2005 server (using sp_startjob or some more complex trigger mechanism).