在SQL Job中作为步骤调用时，SSIS包未运行 [英] SSIS package not running when called as step in SQL Job

问题描述

我有一个.dtsx文件(一个SSIS包)，该文件从FTP服务器下载文件并导入数据.每当我手动运行它时，它运行良好.但是，当我计划在SQL Server代理程序作业中作为一个步骤调用程序包时，它将失败.它失败的步骤是我称为.bat文件的那一步.作业历史记录查看器中的错误是这样的:

I have a .dtsx file (an SSIS package) that downloads files from an FTP server and imports data. It runs fine whenever I run it manually. However, when I schedule calling the package as a step in a SQL server agent job, it fails. The step it fails at is the one where I call a .bat file. The error in the job history viewer says this:

错误:2009-05-26 12:52:25.64
代码:0xC0029151来源:执行 批处理文件执行流程任务
说明:执行中 "D:\ xxx \ import.bat""位于"， 进程退出代码为"1"，而 预期为"0".结束错误DTExec: 包执行返回 DTSER_FAILURE(1).

Error: 2009-05-26 12:52:25.64
Code: 0xC0029151 Source: Execute batch file Execute Process Task
Description: In Executing "D:\xxx\import.bat" "" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1).

我认为这是一个权限问题，但是我不确定如何解决此问题.作业所有者是管理员用户，因此我已经证实他们具有.bat文件所在目录的权限.我尝试进入服务并更改SQL Server代理的登录身份"选项，但两个选项均无效(本地系统帐户和此帐户).有没有人想过要调整其他权限才能使其正常工作?

I think it's a permissions issue, but I'm not sure how to resolve this. The job owner is an admin user, so I've verified they have permissions to the directory where the .bat file is located. I've tried going into Services and changing the "Log On As" option for SQL Server Agent, and neither option works (Local System Account and This Account). Does anyone have ideas as to what other permissions need to be adjusted in order to get this to work?

推荐答案

我尝试只执行批处理文件作为SQL Job步骤，它提供了更多细节.结果表明，当我尝试调用可执行文件时，它失败了，该可执行文件与.bat文件位于同一目录中，但不在Windows/system32目录中，而该目录是从中执行的.

I tried executing just the batch file as a SQL Job step, and it gave more specifics. It showed that it failed when I was trying to call an executable, which was in the same directory as my .bat file, but not in the windows/system32 directory, which is where it was executing from.

我将可执行文件移到了system32目录，但是后来我不知道文件被下载到了哪里.然后，我发现执行流程任务(执行.bat的属性)有一个名为WorkingDirectory的属性.我将其设置为bat所在的目录，将可执行文件移回到与.bat文件相同的目录中，并且现在可以正常工作了.

I moved the executable to the system32 directory, but then I had no clue where my files were being downloaded to. Then I found that there's a property for the Execute Process Task (the one that executes the .bat) called WorkingDirectory. I set this to be the directory where the bat is located, moved the executable back into the same one as the .bat file, and it's now working as expected.

这篇关于在SQL Job中作为步骤调用时，SSIS包未运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！