SSIS 包在被代理调用时不执行任何操作
SSIS 包循环遍历输入文件。对于每个文件，平面文件解析将记录添加到数据库表中，然后重命名/移动文件以进行存档。在所有文件之后，程序包调用存储过程来删除所有一年前的记录。
包从 Visual Studio 运行正常。放入SSIS包存储中，从那里运行，没有问题。
创建 SQL 代理作业来运行包。作业执行某事大约五分钟，宣布它成功，但数据库中没有新记录，也没有重命名输入文件。
包使用 SQL Server 权限的专用登录名。作业作为 HOSTNAME-SVC 运行，它对输入目录和存档目录具有读/写权限。
SSIS package loops through input files. For each file, flatfile parse adds records to a DB table, then file is renames/moved for archiving. After all files, package calls a sproc to delete all year-old records.
Package runs from visual studio OK. Put in SSIS package store, run from there, no problem.
Create an SQL Agent job to run package. Job does something for about five minutes, announces it was successful, but no new records in DB and no renaming of input files.
Package uses dedicated login for SQL Server privileges. Job is run as HOSTNAME-SVC which has read/write privileges on the input directory and the archive directory.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您是否为包设置了日志记录？您可以将脚本任务添加到 For-Each 循环容器，该容器在每个循环期间运行 Dts.Events.FireInformation 命令。这可以帮助您跟踪它找到的文件名、循环次数、每个循环需要多长时间等。您还可以在末尾添加一个日志记录步骤，以便您知道它至少退出了 For-Each 循环容器成功。
如果您发现该包运行成功但根本没有循环访问任何文件，那么您可能需要使用一个更简单的包进行测试，该包仅读取一个文件并将其加载到临时表中。如果有效，则继续下一步，循环遍历director 中的所有文件，并一遍又一遍地仅导入一个文件。如果有效，则继续执行下一步，更改文件连接以匹配在 For-Each 循环容器文件枚举器任务中找到的文件。
如果该包没有循环遍历任何文件，并且您甚至无法让它看到您测试从作业加载的一个文件，那么请尝试使用您的凭据创建一个代理帐户并作为代理帐户运行该作业。如果有效，那么您的服务帐户可能存在权限问题。
如果即使使用代理帐户，包也没有导入任何内容，那么您可能需要以服务帐户身份登录服务器，并尝试在 BIDS 中运行 SSIS 包。如果可行，那么您可能希望将其部署到服务器并从服务器运行该包（这将真正使用您的计算机，但至少它使用服务器中的 ssis 定义）。如果这有效，请尝试从代理运行该包。
Have you setup logging for the package? You could add a script task to the For-Each Loop Container that runs a Dts.Events.FireInformation command during each loop. This could help you track the file name it finds, the number of loops it does, how long each loop takes, etc. You could also add a logging step at the end so that you know it is at least exiting the For-Each Loop container successfully.
If you find that the package is running successfully but not looping through any files at all, then you may want to test using a simpler package that reads one file only and loads it into a staging table. If that works, then go the next step of looping over all the files in the director and only importing the one file over and over again. If that works, then go the next step of changing the file connection to match the file that it finds in the For-Each Loop Container file enumerator task.
If the package isn't looping over any files and you can't get it to see even the one file you tested loading from the job, then try creating a proxy account with your credentials and running the job as the proxy account. If that works, then you probably have a permissions issue with your service account.
If the package doesn't import anything even with the proxy account, then you may want to log into the server as the service account and try to run the SSIS package in BIDS. If that works, then you may want to deploy it to the server and run the package from the server (which will really use your machine, but at least it uses the ssis definition from the server). If this works, then try running the package from the agent.
我不确定我是否完全理解。该软件包已经在多个 Windows 帐户下进行了彻底测试，它确实找到了所有文件并重命名了所有文件。
在代理下，它绝对不执行任何可见操作，但需要五分钟才能完成。没有权限错误或任何其他错误。我没有提到早期的尝试确实遇到了权限错误，因为我们未能授予服务帐户对输入和输出目录的访问权限。
我无法以服务帐户身份登录来尝试该操作，因为我没有密码。但是 sa isd 作业所有者，因此它应该能够切换到服务帐户 - 我们十天前收到的访问错误表明它可以。包裹本身在这十天里没有改变。我们刚刚删除了该作业，以便对部署过程进行完整的“彩排”。
因此，我认为改变的是部署过程中的一些细节，不幸的是，它在成功时不在源代码管理中。
I'm not sure I fully understand. The package has already been thoroughly tested under several Windows accounts, and it does find all the files and rename all the files.
Under the Agent, it does absolutely nothing visible, but takes five minutes to do it. NO permissions errors or any other errors. I didn't mention that an earlier attempt DID get permissions errors because we had failed to give the service acount access to the input and output directories.
I cannot log in as the service account to try that because I do not have a pasword for it. But sa isd job owner so it should be able to switch to the service account--and the access errors we got ten days ago show that it can. The package itself has not changed in those ten days. We just deleted the job in order to do a complete "dress rehearsal" of deployment procedure.
So what has changed, I presume, is some detail in the deployment procedure, which unfortunately was not in source control at the time it succeeded.
权限方面似乎有些不同。我们通过允许“每个人”读取生产服务器上的目录来解决这个问题。由于某种未知的原因，我们不必在测试服务器上执行此操作。
当作业尝试获取文件列表时，它没有收到错误（将被记录），而是得到一个空列表。为什么循环一个空列表需要五分钟仍然是一个谜，缺乏权限也是一个谜。但至少发生了什么已经确定。
It seems to be something different about the permissions. We made the problem go away by allowing "everyone" to read the directory on the production server. For some unknown reason, we did not have to do that on the test server.
When the job tried to fetch the file list, instead of getting an error (which would be logged) it got an empty list. Why looping through an empty list took five minutes is still a mystery, as is the lack of permissions. But at least what happened has been identified.
我有类似的问题。能够通过设置 SQL Server 代理作业的日志记录选项来弄清楚发生了什么。
编辑运行包的作业中的步骤，转到日志记录选项卡并选择“SQL Server 的 SSIS 日志提供程序”，然后在配置字符串中，我选择（使用下拉菜单）包中的 OLEDB 连接器，它恰好连接到有问题的 SQL Server。
然后，我能够查看该作业历史记录的更多详细信息，并确认它没有找到文件。通过更改目录的权限以匹配sql server代理帐户，包最终正确执行。
希望这有帮助。
解决问题后，您可能需要关闭日志记录，具体取决于包运行的频率以及日志记录在您的情况下提供的信息量。
问候，
贝尔廷
I had a similar problem. Was able to figure out what was happening by setting the logging option of the SQL Server Agent Job.
Edit the step in the job that runs the package, go to the logging tab and pick "SSIS log provider for SQL Server" and, in the configuration string, I picked (using the drop down) the OLEDB connector that was in the package, it happens to connect to SQL Server in question.
I was then able to view more details in the history of that job, and confirmed that it was not finding files. By changing permissions on the directory to match the sql server agent account, the package finally executed properly.
Hope this helps.
You may want to turn logging off after you resolve your issue, depending on how often your package will run and how much information logging provides in your case.
Regards,
Bertin