使用dtexec运行SSIS包 [英] Running an SSIS Package using dtexec

问题描述

我正在使用dtexec运行SSIS程序包.该软件包在我的系统上的BIDS中运行良好.当我创建一个SQL Server代理作业以按计划运行程序包时.程序包运行步骤被安排为T-SQL任务，而不是SSIS程序包之一).该作业未报告任何错误，但是甚至没有在服务器上我想要的目标位置创建输出excel文件.

I'm running an SSIS package using dtexec. The package runs fine in BIDS on my system. When I create an SQL server agent job to run the package on a schedule. The package running step is scheduled as a T-SQL task, not an SSIS package one). The job reports no error, but it's not even creating the output excel file @ my desired destination on the server.

此外，当我在命令外壳中单独运行命令时，它返回的错误如下所示.间歇性地，它还会在我用来复制文件的FileSystem Task上返回错误，指出源或目标都不存在！当BIDS中相同的变量值对我有用时，为什么SQL作业会失败?

Furthermore, when I separately run the command in command shell, it's returning me the errors shown below. Intermittently, it'll also return errors on the FileSystem Task that I use to copy files, saying that either the source or destination doesn't exist!! When the same variable values work for me in BIDS, why is the SQL job failing?

Started:  7:33:27 PM
Error: 2012-10-26 19:33:27.60
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0
x8009000B "Key not valid for use in specified state.". You may not be authorized
to access this information. This error occurs when there is a cryptographic err
or. Verify that the correct key is available.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC00F9304
   Source: GICSReport Connection manager "Excel Connection Manager"
   Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Conne
ction Manager is not supported in the 64-bit version of SSIS, as no OLE DB provi
der is available.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC020801C
   Source: Data Flow Task Excel Destination [22]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER.  The AcquireConnection method call to the connection manager "Excel Connecti
on Manager" failed with error code 0xC00F9304.  There may be error messages post
ed before this with more information on why the AcquireConnection method call fa
iled.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC0047017
   Source: Data Flow Task SSIS.Pipeline
   Description: component "Excel Destination" (22) failed validation and returne
d error code 0xC020801C.
End Error
Error: 2012-10-26 19:33:27.78
   Code: 0xC004700C
   Source: Data Flow Task SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2012-10-26 19:33:27.79
   Code: 0xC0024107
   Source: Data Flow Task
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  7:33:27 PM
Finished: 7:33:27 PM
Elapsed:  0.343 seconds

请帮助！:) ....我应该将所有变量，连接管理器以及所有内容添加到我的配置文件中吗?目前，我只添加了一些ppty值的变量和连接管理器，但似乎没有一个组合可以有效地工作.

Pls help! :) .... Should i be adding all the variables, connection managers, and everything to my config file? currently i've only added some ppty values of the variables and connection managers, but no combo seems to work effectively.

推荐答案

我要解决的第一个错误是"64位版本的SSIS不支持Excel Connection Manager，因为没有可用的OLE DB提供程序."

The first error I would address is "The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available."

开箱即用的Excel驱动程序仅存在于32位地址空间中.BIDS/SSDT是32位应用程序，因此Excel源和目标工作正常.但是，当从命令行/SQL Agent运行它们时，则需要显式使用DTEXEC程序的32位版本.

The out of the box Excel drivers only exist in the 32 bit address space. BIDS/SSDT is a 32 bit application so Excel source and destinations work just fine. However, when you run them from the commandline/SQL Agent, then you need to explicitly use the 32 bit version of the DTEXEC program.

第1步将确保您可以在代理作为您自己执行的服务器上从命令行运行软件包.假设您的SQL Server安装在常规位置，则您可能可以使用以下DTEXEC.exe之一

Step 1, will be to ensure you can run the package from the command line on the server the agent executes on as yourself. Assuming your SQL Server is installed in the customary location, you probably have one of the following DTEXEC.exe available to you

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe

您将要使用(x86)版本.将来的读者，如果您碰巧使用的是32版本的Windows(也许是Windows 2003)，则前3个将是您唯一可用的选项.正如Vivek的错误消息所示，他正在以64位模式执行SSIS程序包.

You will want to use the (x86) version. Future readers, if you happen to be on a 32 version of Windows (Windows 2003, maybe), the first 3 will be the only options available to you. As Vivek's error message has indicated, he is executing an SSIS package in 64 bit mode.

dtexec 提供了一个命令-line开关/X86 ，可让您无缝地将同一可执行文件用于32位和64位操作.谎言！文档确实指出了问题，但谁在阅读文档?

dtexec provides a command-line switch /X86 to allow you to seamlessly use the same executable for both 32 and 64 bit operations. LIES! The documentation does call that out but who reads documentation?

此选项仅由SQL Server代理使用.该选项被忽略如果您在命令提示符下运行dtexec实用程序.

This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

因此，您需要通过提供显式路径来运行软件包

So, you will need to run your package by providing the explicit path

C:\ Program Files(x86)\ Microsoft SQL Server \ 100 \ DTS \ Binn \ DTExec.exe/file C:\ folder \ GICSReport.dtsx

我在输出中看到无法解密受保护的XML节点"，并且还声明您正在使用配置文件，因此您很有可能将PackageProtectionLevel从默认的EncryptSensitiveWithUserKey更改为DontSaveSensitive.该功能可以防止意外泄露敏感数据(密码)，但是由于您已经使用配置文件进行了处理，因此这不会成为问题.……考虑到这一点，这实际上可能是来自其他软件包保护级别之一的错误.

I see "Failed to decrypt protected XML node" in your output and you also state you are using configuration files so you can most likely change your PackageProtectionLevel from the default EncryptSensitiveWithUserKey to DontSaveSensitive. That feature exists to prevent accidental exposure of sensitive data (passwords) but since you are already handling that with config files, that should not be an issue. ... That might actually be an error from one of the other package protection levels now that I think about it.

无论如何，请先尝试从32位可执行文件运行.如果这样不起作用，请尝试按照指示更改包装保护级别.如果其中任何一个使程序包都能按预期运行，请尝试从SQL Agent运行相同的命令.

At any rate, try running from the 32 bit executable first. If that doesn't work try changing the package protection level as indicated. If either of those make the package run as expected, then attempt to run the same command from the SQL Agent.

如果所有方法都奏效，请将其标记为答案.如果没有，请更新票证，并生成当前错误，我们将要求提供更多信息.

If it all works, mark this as the answer. If not, please update the ticket with the current error being generated and we'll ask for more information.

这篇关于使用dtexec运行SSIS包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！