2006/06/05

SSIS package execution fails when you run it from a SQL Server Agent job

I created a SQL Server Integration Services (SSIS) package. It was pretty simple and it worked fine. After testing it, I created a SQL Server job with a single step that was supposed to execute the package.

And it took me two days to make it work. Unfortunately, documentation dealing with this topic is pretty poor.

I was getting different errors - for example:

Executed as user: SOME_DOMAIN\sqlagent2005. The package could not be found.
The step failed.

Unable to start execution of step 1 (reason: Could not get proxy data for
proxy_id = 2). The step failed.

This is a method I found (I am positive that there are other ways to get the same result, too).

1. Create a credential (make sure that the chosen account can execute jobs).

2. Create an SSIS proxy using this credential.

3. Set the proxy in the 'Run as' combo in a job step definition.

4. Grant access to C:\Program Files\Microsoft SQL Server\90\DTS\Packages (or wherever you deployed your SSIS package) to the domain account that you used to create the credential.

UPDATE:

Later I found a good article about this problem - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.

No comments:

Post a Comment