使用dtexec运行SSIS包

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.

我正在使用dtexec运行SSIS包。我的系统上的BIDS运行正常。当我创建SQL Server代理程序作业以按计划运行程序包时。程序包运行步骤被安排为T-SQL任务,而不是SSIS程序包)。作业报告没有错误,但它甚至没有在服务器上创建输出excel文件@我想要的目标。

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?

此外,当我在命令shell中单独运行命令时,它会返回下面显示的错误。间歇性地,它还会返回我用来复制文件的FileSystem Task上的错误,说源或目标不存在!!当相同的变量值在BIDS中适用于我时,为什么SQL作业失败?

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

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.

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

2 个解决方案

#1

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."

我要解决的第一个错误是“64位版本的SSIS不支持Excel连接管理器,因为没有可用的OLE DB提供程序。”

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.

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

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

步骤1,将确保您可以从代理程序执行的服务器上的命令行运行程序包。假设您的SQL Server安装在惯用位置,您可能拥有以下DTEXEC.exe之一

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

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.

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

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?

dtexec提供了一个命令行开关/ X86,允许您为32位和64位操作无缝地使用相同的可执行文件。谎言!文档确实称之为但是谁阅读文档?

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

此选项仅供SQL Server代理使用。如果在命令提示符下运行dtexec实用程序,则忽略此选项。

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

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

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.

我在输出中看到“无法解密受保护的XML节点”,并且您还声明您正在使用配置文件,因此您很可能将PackageProtectionLevel从默认的EncryptSensitiveWithUserKey更改为DontSaveSensitive。该功能的存在是为了防止敏感数据(密码)的意外泄露,但由于您已经使用配置文件处理了该功能,因此这应该不是问题。 ...这可能实际上是我想到的其他一个包保护级别的错误。

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.

无论如何,首先尝试从32位可执行文件运行。如果这不起作用,请尝试按指示更改包保护级别。如果其中任何一个使程序包按预期运行,则尝试从SQL代理运行相同的命令。

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.

如果一切正常,请将此标记为答案。如果没有,请使用当前生成的错误更新故障单,我们会要求您提供更多信息。

#2

Here is the solution if you are trying to run a SSIS package with dtexec from transact SQL that contains an excel import.

如果您尝试使用包含excel导入的transact SQL中的dtexec运行SSIS包,则可以使用以下解决方案。

Create a batch file that contains the following code.

创建包含以下代码的批处理文件。

cd C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ DTEXEC.exe /DE "password" /F "C:\mySSISfolder\package.dtsx"

cd C:\ Program Files(x86)\ Microsoft SQL Server \ 100 \ DTS \ Binn \ DTEXEC.exe / DE“password”/ F“C:\ mySSISfolder \ package.dtsx”
Create a shortcut pointing at your batch file, set the properties\advanced on the shortcut to run as an administrator.

创建指向批处理文件的快捷方式,在快捷方式上设置属性\ advanced以管理员身份运行。
from your transact sql procedure run the following command

从您的transact sql过程运行以下命令

exec xp_cmdshell 'C:\myfolder\runssis_sc.lnk'

exec xp_cmdshell'C:\ myfolder \ runssis_sc.lnk'

#1