ssis - 为什么在 dtexec 上运行良好的包在 SQL Server 代理作业下无法运行？

Question

我在本地 C: 驱动器上有一个包，如果我将其作为 SQL 代理作业的一部分运行，则会失败:

02/20/2013 17:38:45,MyUpload,Error,3,FMF-S3-1507\EOS01,PAM_MyUpload,Run MyUpload SSIS Package,,
Executed as user: MARKETS\SVCSQLDEV. 
Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  17:38:45  Error: 2013-02-20 17:38:45.72     
Code: 0xC0011007     
Source: {14BE11F5-B737-4A6E-96E6-111635631749}      
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.  End Error  
Error: 2013-02-20 17:38:45.72     
Code: 0xC0011002     
Source: {14BE11F5-B737-4A6E-96E6-111635631749}      
Description: Failed to open package file "C:\MyFolder\Package.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.  End Error  
Could not load package "C:\MyFolder\Package.dtsx" because of error 0xC0011002.  
Description: Failed to open package file "C:\MyFolder\Package.dtsx" due to error 0x80070003 "The system cannot find the path specified.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.  
Source: {14BE11F5-B737-4A6E-96E6-111635631749}  
Started:  17:38:45  
Finished: 17:38:45  
Elapsed:  0.047 seconds.  
The package could not be found.  The step failed.
,00:00:00,0,0,,,,0

但是，如果我从 SQL 代理作业步骤复制命令行并使用 DTExec.exe 运行它，则会成功:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>DTExec.exe /FILE "C:\MyFolder\Package.dtsx"  /CHECKPOINTING OFF /REPORTING E
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started:  17:44:00
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  17:44:00
Finished: 17:44:56
Elapsed:  55.266 seconds

我可以在作业步骤中尝试解决什么问题？ Commandshell 类型的作业步骤是正确的前进方向吗？

Answer 1

我认为您遇到了权限问题。如果您查看您发布的日志，由于错误 0x80070003“系统找不到指定的路径。”而无法打开包文件“C:\MyFolder\Package.dtsx”。

这是什么意思？

运行 SQL 代理的帐户无权访问 C:\MyFolder。

分辨率

您可能需要向该帐户授予文件系统权限。或者，您可以创建授权凭据并更改作业步骤以使用该帐户。

来自评论

could you advise what you mean by "grant file system to that account"

Windows 在文件夹/对象级别定义权限。如果您右键单击文件夹并选择属性，则会出现一个“安全”选项卡(假设您有权查看它)。其中列出了有权访问该文件夹的组或用户名，然后列出了与该组或用户名关联的权限。

权限选项包括:完全控制、修改、读取和执行、列出文件夹内容、读取、写入和特殊权限。

在这种情况下，运行包 MARKETS\SVCSQLDEV 的用户无权查看/读取 C:\MyFolder\Package.dtsx

用户可能无法列出文件夹内容，或者可以列出内容但无法从那里读取内容。我不是安全人员，因此任何与此相关的建议都应该向您的安全人员提出。

也就是说，我将单击顶部的“编辑”按钮，然后添加用户。您可能需要更改“从此位置”以指向您的域而不是本地计算机。不管怎样，找到该用户并单击“确定”。

然后在“权限”部分中，选中“列出文件夹”和“读取”。单击“确定”关闭该屏幕，然后再次单击“确定”关闭“安全”选项卡。假设您有权执行所有这些操作，则该帐户现在具有对该文件夹的读取/列出访问权限。