SQL Agent job fails when trying to execute SSIS package due to some resolution issues
I have deployed the ssis package to sql 2012. After that the sql execution is scheduled to run this ssis package weekly. When I first started the job, it failed the following error.
Failed to complete the task. The job was called by User MyDomain \ MyUserName. the last step to start was step 1 (planning for the ssis package).
Runs as user: NT Service \ SQLSERVERAGENT. Microsoft (R) SQL Server Run Batch Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 14:17:12 The package was executed on the IS server. Execution ID: 6, Execution Status: 4. To view execution details, right-click the Integration Services Catalog and open the [All Executions] report Started: 2:17:12 PM Finished: 2:17:17 PM Elapsed: 4.493 Seconds ... Package execution failed. The step failed.
I'm sure there are some permissions issues, however I cannot resolve it.
This package deletes the contents of a folder present on my desktop. SQL Server Instance is running on my machine. I'm on a separate machine.
source to share
After searching for articles, I got an article that helped me identify the actual error: Identifying the problem
- Enter sql server instance
- Go to the catalogs of integration services
- Right click on your SSISDB directory and select "All Reports" → "Standard Reports" → "All Executions"
- For your folder, click "All messages for failed task".
- You will see your mistake here.
In my case the error was "Access to path" C: \ Users \ MyUser \ Desktop \ Test Folder 'denied ". Note that there is a Caller section in this report that is trying to access this folder, in my case it was NT SERVICE \ SQLSERVERAGENT .
Go to this folder, right click -> Properties -> Security In the group / username click Edit -> Inside the new popup Add new user Now if you try to find the NT SERVICE \ SQLSERVERAGENT user, you won't find it using the service account. which means that you will see a user named SERVICE . This is the user you should add to this folder.
As soon as I added this, my work started :)
source to share
In my case Identify the problem: Log in. Sample SQL Server Instance. Navigate to the Integration Service directories. Right click on SSISDB directory and select All Reports -> Standard Reports -> All Runs -check, Crash Report Section / Red Error Log (Overview, All Messages, Execution Performance) - I went to Overview -> clicked the execution path -> in messages about errors clicked browsing context, this will show you step by step. I had a datatype mismatch as I was re-running the sql script in ssms, this was giving the problem. Once I fixed the data type or deleted the extra data. Everything is back to normal
source to share
Solution: I got the same error in the SQL agent job, so I just check the SSIS package to see if it works or not, and then find that the package doesn't work because from proc we are taking input as a string, and in the package I created a variable datatype to store the proc input. When I changed that my SSIS package as well as the sql job now works fine. I hope this helps you ..
source to share