由于某些权限问题，尝试执行SSIS包时，SQL Agent Job失败(SQL Agent Job failes when trying to execute SSIS package due to some permission issues)

I deployed ssis package on sql server 2012. Thereafter scheduled a sql job to run this ssis package weekly. When I started a job for the first time, it failed with following error.

The job failed. The Job was invoked by User MyDomain\MyUserName. The last step to run was step 1 (scheduling ssis package).

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:17:12 PM Package execution on IS Server failed. Execution ID: 6, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 2:17:12 PM Finished: 2:17:17 PM Elapsed: 4.493 seconds. The package execution failed. The step failed.

I am sure there is some issue with permissions, however I am not able to resolve this.

This package is deleting a content of a folder present on my desktop. SQL Server instance is running on my machine. I am on a standalone machine.

解决方案

After searching lot of articles I got an article which helped me identifying actual error :- Identifying issue

  1. Login sql server instance
  2. Navigate to Integration Service Catalogs
  3. Right Click on your SSISDB catalog and select All Reports -> Standard reports -> All Executions
  4. For your folder, click on All Messages for failed task
  5. Here you will see your error

In my case error was "Access to path 'C:\Users\MyUser\Desktop\Test Folder' is denied". To note there is a section Caller in this report who is trying to access this folder, in my case it was NT SERVICE\SQLSERVERAGENT.

Go to this folder, right click -> Properties -> Security Under Group/username click Edit -> Inside new pop up Add new user Now if you are trying to find NT SERVICE\SQLSERVERAGENT user, you won't find since its under a service account which means you will see a user named SERVICE . This is the user you need to add to this folder.

As soon as I added this, my job started running :)

 

我在sql server 2012上部署了sis软件包。此后，安排了一个sql作业每周运行一次此sis软件包。
当我第一次开始工作时，它失败并出现以下错误。



工作失败。作业由用户MyDomain\MyUserName调用。
的最后一步是步骤1（计划sis软件包）。



以用户身份执行：NT Service\SQLSERVERAGENT。 Microsoft（R）SQL Server
执行软件包实用程序版本11.0.5058.0，用于64位版权所有（C）
Microsoft Corporation。版权所有。开始时间：2:17:12 PM
IS服务器上的程序包执行失败。执行ID：6，执行
状态：4。要查看执行的详细信息，请右键单击
Integration Services目录，然后打开[所有执行]报告
开始时间：2:17:12 PM完成时间：2:17:17 PM ：4.493秒
程序包执行失败。步骤失败。



我确定权限存在问题，但是我无法解决。


此软件包正在删除桌面上存在的文件夹的内容。
SQL Server实例正在我的计算机上运行。我在一台独立的机器上。

 

解决方案

在搜索了很多文章之后，我得到了一篇文章，可以帮助我识别实际错误：-
识别问题




    1. 登录sql服务器实例

 

    1. 导航到Integration Service目录

 

    1. 右键单击SSISDB目录，然后选择"所有报告->标准报告->所有执行"

 

    1. 对于您的文件夹，单击"所有失败任务的消息"
    2. 在这里您会看到错误



在我的情况下，错误是"访问路径'C： "用户""我的用户""桌面""测试文件夹"被拒绝"。请注意，此报告中有一个"呼叫者"部分正在尝试访问此文件夹，在我的情况下是 NT SERVICE\SQLSERVERAGENT 。


转到此文件夹，右键单击->属性->安全
在"组/用户名"下，单击"编辑"->在新弹出窗口中添加新用户
现在，如果您要查找NT SERVICE\SQLSERVERAGENT用户，您将找不到该帐户，因为该帐户位于服务帐户下，这意味着您将看到一个名为 SERVICE 的用户。这是您需要添加到此文件夹的用户。


我添加此代码后，我的工作开始运行：）

posted @ 2021-06-01 17:56  KJXY  阅读(688)  评论(0)  编辑  收藏  举报