在 SQL 作业中作为步骤调用时 SSIS 包未运行
我有一个 .dtsx 文件（一个 SSIS 包），它从 FTP 服务器下载文件并导入数据。 每当我手动运行它时它都运行良好。 但是，当我安排调用该包作为 SQL Server 代理作业中的一个步骤时，它会失败。 它失败的步骤是我调用 .bat 文件的步骤。 作业历史记录查看器中的错误说明如下：
错误：2009-05-26 12:52:25.64
代码：0xC0029151 来源：执行 批处理文件执行进程任务
描述：执行中 “D:\xxx\import.bat”“”位于“”， 进程退出代码为“1”，而 预期为“0”。 结束错误 DTExec： 包执行返回 DTSER_FAILURE (1)。
我认为这是一个权限问题，但我不知道如何解决这个问题。 作业所有者是管理员用户，因此我已验证他们有权访问 .bat 文件所在的目录。 我尝试进入“服务”并更改 SQL Server 代理的“登录身份”选项，但这两个选项都不起作用（本地系统帐户和此帐户）。 有谁知道需要调整哪些其他权限才能使其正常工作？
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:
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).
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我尝试仅将批处理文件作为 SQL 作业步骤执行，它提供了更多细节。 它表明当我尝试调用可执行文件时失败，该可执行文件与我的 .bat 文件位于同一目录中，但不在 windows/system32 目录中，而该目录是它执行的位置。
我将可执行文件移至 system32 目录，但随后我不知道我的文件被下载到哪里。 然后我发现执行进程任务（执行 .bat 的任务）有一个名为“WorkingDirectory”的属性。 我将其设置为 bat 所在的目录，将可执行文件移回与 .bat 文件相同的目录中，现在它可以按预期工作。
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.
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.
对我来说这是一个权限问题。 转到环境 --> 目录，然后将本地目录更改为 SQLAgentUser 可以访问的目录。 我用的是 C:\temp。 单击“保存”下拉列表，然后选择“设置默认值”。
For me it was a permissions issue. Go to Environment --> Directories, then change Local directory to something the SQLAgentUser can access. I used C:\temp. Click the dropdown for Save, and choose "Set defaults".
您是在批处理文件中执行 SSIS 作业，还是批处理文件是 SSIS 控制流中的一个步骤？
我假设这个答案是后者。 您使用什么任务来执行批处理文件（例如简单的执行程序任务或脚本任务）。 如果是后者，看起来您的批处理文件实际上在某个步骤上失败了，而不是 SSIS 脚本。 我会检查你的批处理文件试图访问的权限
事实上，将批处理文件重写为SSIS中的脚本任务可能是一个更好的主意，因为你会得到更好的错误报告（它会告诉你您脚本中的哪一步失败了）。
您可以尝试在命令窗口中使用 runas 命令执行批处理文件。 如果您尝试在本地系统或网络系统帐户下执行它，它应该会给您一个更好的错误。 如果出现错误，您可以通过“echo %ERRORLEVEL%”检查错误级别。
如果不是后者，并且您通过批处理文件执行 SSIS 包，为什么？
Are you executing the SSIS job in the batch file, or is the batch file a step in the SSIS control flow?
I'm assuming the latter for this answer. What task are you using to execute the batch file (e.g. simple execute program task or a script task). If the latter, it looks like your batch file is actually failing on some step, not the SSIS script. I'd check the permissions of what your batch file is trying to access
In fact, it might be a better idea to rewrite the batch file as a script task in SSIS, because you'll get much better error reporting (it'll tell you which step in the script fails).
You could try executing the batch file using the runas command in a command window. If you try and execute it under the local system or network system account, it should give you a better error. If it does error, you can check the error level by going "echo %ERRORLEVEL%".
If it wasn't the latter, and you're executing the SSIS package via a batch file, why?
您是否可能访问 .bat 文件中的映射驱动器？ 如果是这样，您不能依赖服务内的映射驱动器，因此您必须使用 UNC 路径。
Are you possibly accessing a mapped drive in your .bat file? If so, you can't rely on the mapped drive from within the service, so you'd have to use UNC path.
我遇到了同样的错误，我通过登录到运行该作业的用户帐户来解决它，在那里打开有问题的 Coreftp 站点，测试站点访问，在那里进行更改（在我的情况下，我必须重新输入新密码）现在可以了。
所以是的，这是文件访问的问题。 这是对相关 coreftp 站点的文件访问。
I had the same error and I resolved it by logging on to the user account that runs the job, opened Coreftp site in question there, test the site access, made the change there (in my case, I had to reenter the new password) and now it works.
So yes, it is an issue of file access. This one is file access to the coreftp site in question.