SQL server proxy job execution SSIS package failure solution, sqlssis

SQL server proxy job execution SSIS package failure solution, sqlssis

RT, the execution failed, always prompts "failed to execute as xxxx user", it is difficult to find the reason.

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

Sql2005 how to run ssis (DTS) package with dtexec

1. First, design the package in Business Intelligence and pass the debugging.

2. Use the dtexec tool to run the package

(1) Enable the xp_cmdshell Option

The xp_cmdshell option introduced in SQL Server 2005 is the Server configuration option, allowing the system administrator to control whether xp_cmdshell extension stored procedures can be executed on the system. By default, the xp_cmdshell option is disabled on the newly installed software, but you can enable it by using the peripheral application configurator tool or running the sp_configure system stored procedure, the following code example is as follows:

 To allow advanced 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

(2) Use the dtexec utility to execute the package

Method 1: directly allow ssis File Execution

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

Method 2: import the package into SQL 2005 before execution

1) import package

SQL2005 open Managemenet Studio, select Integration Services, select "stored package"-"MSDB", right-click to import the package, and select File System, specify the package prepared by Business Intelligence Development Studio, and select Import

Note: In the protection level option, you must choose to rely on server storage and role for access control.

Otherwise, an error will be reported when you run the package through dtexec-Note: The protected XML node "DTS: Password" cannot be decrypted, with the error 0x8009000B "This item is not suitable for use in the specified state .". You may not be authorized to access this information. This error occurs when an encryption error occurs. Ensure that the correct key is provided.

2) After the import is complete, the statement can be executed in Managemenet Studio.

xp_cmdshell 'dtexec /DTS "\MSDB\wangluo" /SERVER "XXW2006_1" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V '