使用dtexec实用程序执行SSIS包

I have made a SSIS package to create an XML file, which works fine in Visual Studio, but when I try to run in cmd prompt using dtexec /f "C:\OakeyCreek.dtsx" it throws an error

我已经创建了一个SSIS包来创建一个XML文件,它可以在Visual Studio中正常工作,但是当我尝试使用dtexec / f“C:\ OakeyCreek.dtsx”在cmd提示符下运行时会抛出一个错误

SSIS Package "TestOakey" failed with exit code: 1

SSIS包“TestOakey”失败,退出代码:1

In the package a sqltask gives resultset as XML and a script task creates the xml file.

在包中,sqltask将resultset作为XML提供,脚本任务创建xml文件。

I did some research, same package without script task run fine both studio and cmd prompt. I am suspecting problem with script task, because I have both VS2005 and VS2008 on the same machine.

我做了一些研究,相同的包没有脚本任务运行良好的工作室和cmd提示。我怀疑脚本任务有问题,因为我在同一台机器上同时拥有VS2005和VS2008。

Any idea how to resolve this problem ?

知道如何解决这个问题吗?

Exact error ms popup is ...

弹出的确切错误ms是......

Registration information for this application needs to be updated.To update, log on as an administrator and run this command.

需要更新此应用程序的注册信息。要更新,请以管理员身份登录并运行此命令。

"C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\VSTA.exe" /HostID SSIS_ScriptTask /setup

“C:\ Program Files \ Microsoft Visual Studio 9.0 \ Common7 \ IDE \ VSTA.exe”/ HostID SSIS_ScriptTask / setup

My operating system is Vista

我的操作系统是Vista

2 个解决方案

#1

Note: Edited to provide the answer!

注意:编辑提供答案!

So you've set up a SQL Server Agent job, and now you can't write to the file system. This is expected behavior, since SQL Server Agent uses SQL Server credentials to launch the job, not Windows credentials. So, what you'll have to do is this:

因此,您已设置SQL Server代理作业,现在无法写入文件系统。这是预期的行为,因为SQL Server代理使用SQL Server凭据来启动作业,而不是Windows凭据。那么,你要做的就是:

Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.

在SQL Server Management Studio中的数据库服务器的“安全性”文件夹下,找到“凭据”文件夹。

Right click Credentials and hit New Credential.

右键单击Credentials并单击New Credential。

Set up the Credential as you.

像你一样设置凭证。

Expand SQL Server Agent, and right click Proxies, and hit New Proxy.

展开SQL Server代理,右键单击“代理”,然后单击“新建代理”。

Set up the Proxy as you, using the Credential you just created.

使用您刚刚创建的凭据设置代理服务器。

Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).

使代理具有SSIS子系统(如果是管理员,请检查所有子系统)。

Go edit the step on your job that calls the SSIS package.

编辑调用SSIS包的作业上的步骤。

Change the Run As field from SQL Server Agent to the Proxy that you just set up (if you did it right, it will be the only other choice in the dropdown).

将“运行方式”字段从SQL Server代理更改为刚刚设置的代理(如果操作正确,它将是下拉列表中唯一的其他选项)。

You should be good to go!

你应该好好去!

Cheers,
Eric

#2

You are creating the package using SSIS 2005, but running it using DTEXEC from SSIS 2008 - you probably have both SSIS 2005 and 2008 installed, and DTEXEC 2008 appears first in the path.

您正在使用SSIS 2005创建程序包,但是使用SSIS 2008中的DTEXEC运行它 - 您可能同时安装了SSIS 2005和2008,并且DTEXEC 2008首先出现在路径中。

To use DTEXEC 2005, specify full path, like "c:\program files\Microsoft Sql Server\90\dts\binn\dtexec.exe" /f Package.dtsx

要使用DTEXEC 2005,请指定完整路径,例如“c:\ program files \ Microsoft Sql Server \ 90 \ dts \ binn \ dtexec.exe”/ f Package.dtsx

In theory, DTEXEC 2008 should also be able to run the package (it should upgrade it on the fly to SSIS 2008 format), but there might be bugs and incompatibilities between the versions. Also it seems something is wrong with script task setup in SSIS 2008. Why would not you follow the instructions in the error message: logon as administrator and (using elevated command prompt) run -

从理论上讲,DTEXEC 2008也应该能够运行该软件包(它应该将其升级到SSIS 2008格式),但版本之间可能存在漏洞和不兼容性。此外,SSIS 2008中的脚本任务设置似乎有问题。为什么不按照错误消息中的说明操作:以管理员身份登录并使用提升的命令提示符运行 -

"C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\VSTA.exe" /HostID SSIS_ScriptTask /setup

“C:\ Program Files \ Microsoft Visual Studio 9.0 \ Common7 \ IDE \ VSTA.exe”/ HostID SSIS_ScriptTask / setup

#1