0

I have an SSIS job that contains a script task. There is also a connection to another server within the package that can only be executed accessed by a specific user and the agent cannot be given read rights to the other server. So the solution to this is have the SQL job be run as the required user. The problem with this is this user does not have full control over the folder/file where the script task will be temporarily located while the code runs so I get a failure on the script task but if I run it in visual studio or with the normal agent account the script task executes successfully.

I have tried giving the user access to all major drives on the server but this has not solved the problem

Keag1
  • 141
  • 1
  • 11
  • When you say you get a failure on the script task, what is the exact error message? Because I don't think it's caused by what you think it is. Then again, see if this helps: https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the – Tab Alleman Jul 02 '19 at 18:24
  • Better list out your error, I also dont think this is because of Script Task access problem. – BeiBei ZHU Jul 03 '19 at 02:16
  • The error I get is "Exception has been thrown by the target of an invocation", but I only get this when using the second account not the Agent account which leads me to believe it is permissions related – Keag1 Jul 03 '19 at 08:06
  • if you are use SQL 2017+, you can take a look for Job execution report and find out inner error. and this will help you and us to locate the issue. – BeiBei ZHU Jul 04 '19 at 02:44

1 Answers1

0

Is the script doing something on the folder?

Or does it just need permission to execute script?

I would anyway advise you to implement error handling in script: https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task?view=sql-server-2017

Make sure it works in visual studio, so that when you get that error you are sure that the error is caused by permissions/environment.

You could also take a look at this article: article

Ruben Ravnå
  • 659
  • 7
  • 17