SSIS包运行于SSIS，但不是来自作业。

I have built an SSIS Packaage that imports data on a daily basis.

我建立了一个SSIS包，每天导入数据。

i have tested it in BIDS and it runs fine, i have deployed it to the the SSIS server and it runs fine, but when i schedule it to run as a job it Errors out. i get the errors below. i've added the srvSQLagent account to the the correct group to have access to the folder that causing the problem.. am i missing something stupid here?

我已经在投标中测试过它，它运行得很好，我已经将它部署到SSIS服务器，它运行得很好，但是当我将它安排为一个作业时，它就会出错。我得到下面的错误。我已经将srvSQLagent帐户添加到正确的组中，以访问导致问题的文件夹。我是不是漏掉了什么愚蠢的东西?

===================================================================

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Date 03/07/2013 14:55:06 Log Job History (Import SN2 Data)

日志工作历史(导入SN2数据)

Step ID 1 Server STX-RPT Job Name Import SN2 Data Step Name Run Import Package Duration 00:00:09 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

步骤ID 1服务器STX-RPT作业名称导入SN2数据步骤名称运行导入包持续时间00:00:09 Sql严重性0 Sql消息ID 0操作符已通过电子邮件发送操作符Net发送操作符已分页重试尝试0

Message Executed as user: INT\srvSQLagent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 14:55:06
Error: 2013-07-03 14:55:15.69
Code: 0xC001401E
Source: Servicenet Data Import Connection manager "Downloaded"
Description: The file name "\\FILE\Groups\ISG\England\Servicenet Data\Downloaded" specified in the connection was not valid. End Error
Error: 2013-07-03 14:55:15.69
Code: 0xC0202070
Source: Servicenet Data Import Connection manager "Downloaded"
Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error
Error: 2013-07-03 14:55:15.69
Code: 0xC0029163
Source: File System Task File System Task
Description: File or directory "\\FILE\Groups\ISG\England\Servicenet Data\Downloaded" represented by connection "Downloaded" does not exist. End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 14:55:06
Finished: 14:55:15
Elapsed: 9 seconds.
The package execution failed.
The step failed.

以用户身份执行的消息:INT\srvSQLagent。微软(R) SQL Server执行软件包实用版本10.50.1600.1的32位版权(C)微软公司2010。保留所有权利。代码:0xC001401E源:Servicenet数据导入管理器“已下载”描述:文件名“\文件组\ISG\England\Servicenet Data\ download”在连接中指定为无效。最后错误错误:2013-07-03 14:55:15.69代码:0xC0202070源代码:Servicenet数据导入连接管理器“下载”描述:文件名属性无效。文件名是一个设备或包含无效字符。代码:0xC0029163来源:文件系统任务文件系统任务描述:文件或目录“\\文件\\ \ISG\ \ISG\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\结束错误DTExec:包执行返回DTSER_FAILURE(1)。包执行失败。这一步失败了。

3 个解决方案

#1

The issue had arisen from using a File system task to clear a folder of all Files.

该问题源于使用文件系统任务清除所有文件的文件夹。

I never got to the bottom of the problem, i simply found a different method of deleting the required files.

我从来没有深入到问题的根本，我只是找到了一种不同的方法来删除所需的文件。

#2

It's definitely folder permission issue.

这绝对是文件夹权限问题。

Please give proper rights for everyone.

请给每个人适当的权利。

Alpesh Dhori Software Professional

Alpesh Dhori软件专业

#3

I suppose that next solution will help you:

我想下一个解决方案会对你有所帮助:

Assume that we need to write \\serv\share\dir1..\dirN\targetDir\somefile.txt using SSIS throught SQL Agent Job and nonadmin proxy account MyDomain\TestAccount

假设我们需要编写\serv\共享\dir1. \\ targetDir\somefile。txt使用SSIS的SQL代理作业和非管理代理帐户MyDomain\TestAccount。

1) MyDomain\TestAccount need read/write access to share \\serv\share

1) MyDomain\TestAccount需要读/写访问共享\\serv\共享

2) MyDomain\TestAccount needed at least FILE_READ_DATA permission for all folders (share,dir1,..dirN)

2) MyDomain\TestAccount至少需要所有文件夹的FILE_READ_DATA权限(share,dir1，. dirN)

3) MyDomain\TestAccount needed the CHANGE rights + FILE_DELETE_CHILD permission for folder targetDir

3) MyDomain\TestAccount需要为targetDir文件夹添加更改权限+ FILE_DELETE_CHILD权限

(cacls should show this permissions as following:

(cacls应显示如下权限:

DELETE
READ_CONTROL
SYNCHRONIZE
FILE_GENERIC_READ
FILE_GENERIC_WRITE
FILE_GENERIC_EXECUTE
FILE_READ_DATA
FILE_WRITE_DATA
FILE_APPEND_DATA
FILE_READ_EA
FILE_WRITE_EA
FILE_EXECUTE
FILE_DELETE_CHILD
FILE_READ_ATTRIBUTES
FILE_WRITE_ATTRIBUTES

)

Of course the full rights for targetDir are enough.

当然，targetDir的全部权限就足够了。

If targetDir doesn't has FILE_DELETE_CHILD permission then we see such strange behavior of flat file connection in SSIS. User MyDomain\TestAccount may doing anything with file somefile.txt (read,write,delete) using standard Windows tools. But SSIS Job can't overwrite this file and fail.

如果targetDir没有FILE_DELETE_CHILD权限，那么我们可以在SSIS中看到这种平面文件连接的奇怪行为。用户MyDomain\TestAccount可以用file somefile做任何事情。txt(读、写、删除)使用标准的Windows工具。但是SSIS不能覆盖这个文件并失败。

#1