SQL Server Agent failed to execute SSIS package in Job

RT, failed to execute, always only prompt a "to XXXX user identity execution Failure", it is difficult to find the reason. 

Reference http://bbs.csdn.net/topics/300059148

Sql2005 How to run SSIS (DTS) packages with dtexec

first, in Business intelligence design the package, and debugging through. 

second, the use of dtexec tools to run the package


(i) Open xp_cmdshell option

the xp_cmdshell option introduced in SQL Server 2005 is a server configuration option that enables system administrators to control whether xp_cmdshell extended stored procedures can be executed on the system. By default, the xp_cmdshell option is disabled on newly installed software, but you can enable it by using the Surface Area Configuration tool or by running the sp_configure system stored procedure, as shown in the following code example:


To, advanced options to is changed.

EXEC sp_configure ' show advanced options ', 1 go–


To update the currently configured value for advanced options. RECONFIGURE GO-To enable the feature.


EXEC sp_configure ' xp_cmdshell ', 1 go–chinaz^com

To update the currently configured value for this feature. RECONFIGURE GO


(ii) Implementation of the package using the DTExec utility

Way one: directly by allowing SSIS files to execute

use the following command: xp_cmdshell ' dtexec/f "C:\UpsertData.dtsx"


method Two: Import the package into SQL 2005 before executing


1) Import Package

SQL2005 Open managemenet Studio, select Connect Integration Services, select "Stored Packages"-"MSDB", right-click Import Package, select File System, specify business Intelligence Development Studio-Ready package, select Import


Note: You need to choose to rely on server storage and role for access control in the protection level option


Otherwise, when running the package via dtexec-note: The Protected XML node "Dts:password" cannot be decrypted, and the error is 0x8009000b "The item is not suitable for use in a specified state. ”。 You may not have permission to access this information. This error occurs when there is a cryptographic error. Make sure that you provide the correct key. 
 
2) Execution of statements in Managemenet Studio after import is complete

xp_cmdshell ' Dtexec/dts "\msdb\wangluo"/server "Xxw2006_1"/maxconcurrent "-1"/checkpointing off/reporting V '

Job execution SSIS package in SQL Server Agent failed