SQL Agent Job failes when trying to execute SSIS package due to some permission issues

3
votes

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.

4
sql-serverdeploymentssisactive-directorysql-server-data-tools

4 Answers

14
votes

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 :)

0
votes

In my case Identifying issue: Login sql server instance Navigate to Integration Service Catalogs Right Click on your SSISDB catalog and select All Reports -> Standard reports -> All Executions -check the fail/ red error log report section(overview,all messages,execution performance) - I went to overview ->clicked execution path -> under error messages clicked view context this will show you step by step execution. I had a data type mismatch as I reran the sql script in ssms it was giving issue. Once I fixed the data type or removed extra data. All back to normal

0
votes

Solution : I got the same error in SQL agent job ,so I just check SSIS package whether it is running or not then find that package is failing because from proc we are taking input as string and in package I have create a variable datatype object to store the proc input ,When I changed that my SSIS package as well as sql job is running fine now. I hope this will help you ..

0
votes

I got the same error while executing the SSIS Job in SQL Agent "Access to the path 'C:\Users\abhis\Documents\Visual Studio 2017\Projects\SSIS\AWS-S3-FileLoad\OracleEmployeeDetails' is denied.".

Solution -

Go to this folder for which you are getting the Access Denied Error,

  1. Go to Folder -> Properties -> Security -> To Change Permission Click Edit - > Add the user "SERVICE" to the Folder.

\As soon as the "SERVICE" user has been added, Job has executed and completed successfully.\