If possible, always use Operating system (CmdExec) SQL Server Agent job step and DTExec.exe utility to call an SSIS package from a SQL Server Agent job step. The gives you much more verbose output information than SSIS Package step and makes troubleshooting much easier.
Just compare this:
Executed as user: DOMAIN1\sqlagent2005.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:12:30 AM
Error: 2006-10-27 10:12:30.83
Code: 0xC0014060
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Description: Failed to decrypt a package that is encrypted
with a user key. You may not be the user who encrypted this
package, or you are not using the same machine that was
used to save the package.
End Error
Could not load package "C:\Program Files\Microsoft SQL Server
\90\DTS\Packages\Package1\Package1.dtsx" because of error
0x8009000B.
Description: Failed to remove package protection with error
0x8009000B "Key not valid for use in specified state.". This
occurs in the CPackage::LoadFromXML method.
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Started: 10:12:30 AM
Finished: 10:12:30 AM
Elapsed: 0.438 seconds.
Process Exit Code 5.
The step failed.
and this:
Executed as user: DOMAIN1\Administrator.
The package could not be loaded.
The step failed.
UPDATE:
Also, check the comment below for yet another reason that proves the point I am making here.
If anyone wanders into this post, it's probably because you are getting errors trying to have a job run an SSIS package. We ran into an issue where we were programmatically creating jobs to run packages, passing in parameters on the command line. Seemed to work okay til we created a lot of jobs at the same time and a lot of them would fail with the only message being "unable to load package". We have implemented a locking mechanism to only allow one package at a time to run for application reasons. However, the sql server agent was not able to even load the package to get it to run. We were using the ssis job step. We changed the code to create the job step as CmdExec to run the dtexec command to see if we could get more error information. Lo and behold, the problem went away! We just watched over 30 jobs queue up and run without error. We think that the server agent is actually loading a package multiple times and gets an error when trying to do so. Changing to cmdexec caused most of the jobs to go to 'waiting for process thread' and a handful actually execute simultaneously. So I'm not 100% sure we're out of the woods on this yet but definitely we will be running the packages via dtexec.
ReplyDelete