Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve the problem of failed execution of SSIS packages in sql server agents". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to solve the problem of failed SSIS package execution of jobs in sql server Agent".

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

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

How Sql2005 runs the ssis (DTS) package with dtexec

First, design the package in Business Intelligence and debug it.

Choose dtexec tool to run the package

(1) 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. The xp_cmdshell option is disabled by default on newly installed software, but can be enabled by using the Surface area configuration tool or by running the sp_configure system stored procedure, as shown in the following code example:

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 by allowing the ssis file to execute

Use 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 service, select "stored package"-"MSDB", right-click to import package, select file system, specify package made with Business Intelligence Development Studio, and select Import.

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

Otherwise, an error will be reported when running the package through dtexec-description: the protected XML node "DTS:Password" cannot be decrypted, and the error is 0x8009000B "this item is not suitable for use in the specified state." . You may not have access to this information. This error occurs when an encryption error occurs. Make sure that the correct key is provided.

2) after the import is completed, you can execute the statement in Managemenet Studio

Xp_cmdshell 'dtexec / DTS "\ MSDB\ wangluo" / SERVER "XXW2006_1" / MAXCONCURRENT "- 1" / CHECKPOINTING OFF / REPORTING V' so far, I believe you have a better understanding of "how to solve the problem of failed SSIS package execution in sql server Agent". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.