使用 dtexec 运行 SSIS 包

2021-12-30 00:00:00 export-to-excel sql-server ssis

我正在使用 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.

此外，当我在命令 shell 中单独运行该命令时，它会返回如下所示的错误.间歇性地，它还会在我用来复制文件的文件系统任务上返回错误，说源或目标不存在！！当相同的变量值在 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.

推荐答案

我要解决的第一个错误是SSIS 的 64 位版本不支持 Excel 连接管理器，因为没有可用的 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 代理运行它们时，您需要明确使用 32 位版本的 DTEXEC 程序.

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 FilesMicrosoft SQL Server90DTSBinnDTExec.exe
c:Program FilesMicrosoft SQL Server100DTSBinnDTExec.exe
C:Program FilesMicrosoft SQL Server110DTSBinnDTExec.exe
C:Program FilesMicrosoft SQL Server120DTSBinnDTExec.exe
C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe
C:Program Files (x86)Microsoft SQL Server100DTSBinnDTExec.exe
C:Program Files (x86)Microsoft SQL Server110DTSBinnDTExec.exe
C:Program Files (x86)Microsoft SQL Server120DTSBinnDTExec.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 Server100DTSBinnDTExec.exe/file C:folderGICSReport.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 代理运行相同的命令.

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.

相关文章