Solution for job execution SSIS package failure in SQL Server Agent _mssql

RT, execution failed, always only prompt one sentence "As a XXXX user execution failed", it is difficult to find the reason.

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

Sql2005 How to run SSIS (DTS) packages with dtexec

First, in the Business Intelligence design good package, and debugging through.

Second, choose the DTExec tool to run the package

(i) Open the 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 it can be enabled by using the Perimeter Application Configurator tool or by running the sp_configure system stored procedure, as shown in the following code example:

 To allow advanced the options to be 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 packages using dtexec utility tools

Way one: directly by allowing SSIS file execution

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

Mode two: First to import the package into SQL 2005 in the execution

1) Import Package

SQL2005 Open managemenet Studio, select Integration Services, select "Stored Packages"-"MSDB", right-click on the package, select File System, specify business Intelligence Development Studio Good package, select Import

Note: The protection level option needs to choose to rely on server storage and roles for access control

Otherwise, an error occurs when the package is run via dtexec-note: The Protected XML node "Dts:password" cannot be decrypted and the fault is 0x8009000b "This item is not suitable for use in the 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 After the import is complete, you can execute the statement in Managemenet Studio

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