In the event of a failure the first place to look is the Job History. You may also wish to consider using the Operating System step type over the SSIS step type. For example if the package cannot be loaded you may see an error like this:
message
Executed as user: DOMAIN\sqlserver.jellystone. The package could not be loaded. The step failed.
Unfortunately the SSIS job step sub-system does not capture the full output. Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.
message
Executed as user: DOMAIN\sqlserver.jellystone.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:27:22 Could not load package "\File System\MiWarehouse\Dimensions" because of error 0x80070005.
Description: Access to the path
'C:\Program Files\Microsoft SQL Server\90\DTS\Packages\MiWarehouse\Dimensions.dtsx' is denied.
Source: mscorlib
Started: 11:27:22
Finished: 11:27:22
Elapsed: 0.062 seconds.
Process Exit Code 5. The step failed.
Setting the job step log will also capture usefull information, see the Advanced tab for the job step.
Package Store File Permissions
If using a Windows account that is not a member of the machine Administrators group is used for the SQL Sever Agent service account, you will have to adjust the permissions on the package store folder. For an example of the error produced, see Job History and Job Step Sub-Systems above. In that example the execution context of the job is the Windows account sqlserver.jellystone, which is a member of the Users group only. This does not have Read permissions to the package store folders, so granting Read permissions to this account or the Users group will resolve this issue.
You may experience other similar problems with any files resources, such as configuration files used by the packages, log files you are trying to write too, or the files you are trying to load.
Jet 4.0 OLE DB Provider
The Jet 4.0 OLE DB Provider only comes in a 32-bit flavour, so if your package uses a Jet connection, but you are running on a 64-bit machine, you will need to downgrade in effect downgrade for execution. In a similar manner to above, you can select the 32-bit select Operating System (CmdExec) step type for your job, and use the 32-bit DtExec process, C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DtExec.exe.
