Home > Blockchain >  SQL Server Deployed SSIS package won't open files despite SA account having access to folder
SQL Server Deployed SSIS package won't open files despite SA account having access to folder

Time:11-18

I posted a similar question before, but I have now came back to the point where I need to deal with this and after some tuning I have managed to get rid of all the errors, except one warning that basically tells me that the path provided to the package not finding any files in the directory (which is false). This almost makes me want to believe this is again a permissions issue. As when I run the package locally it all works fine. Here is the warning: enter image description here Same message appears when I right click SSIS package under the catalogue and directly try to execute the job. I made sure that the caller has full permissions to the folder in question

enter image description here

SQL Server Agent Job calls the job like so enter image description here

With a single step in it to execute the SSIS package from the catalogue: enter image description here

The history of the executions is all successful and the history of SQL Agent job is all green
One of the suggestions I got was to open SQL Server Configuration manager but I don't appear to have access to that. I am new to this whole process so I am not sure if authentication has something to do with it or the sa account's permissions.
Note: I am working on this on a remote dev server and not directly on my pc.
Any help would be greatly appreciated.

CodePudding user response:

Since you're running the package as the Agent Service Account:

enter image description here

The permissions need to be applied to the SQL Server Agent Service Account or its per-service SID.

You can see the service account with PowerShell like this:

PS C:\Users\david> (Get-WmiObject win32_service | where Name -eq "SQLSERVERAGENT").StartName
NT Service\SQLSERVERAGENT

CodePudding user response:

Turns out the database instance was on a whole different server that I wasn't even given access to. That server obviously didn't have the path specified in the SSIS package, so what I had to do is create a folder for files in the correct server, reroute my files there, and change the SSIS package path after obtaining the access to the server where DB instance lives. Me being new to all this, it was absolute frustration especially since our team is small and I am new in it I can't just ask someone questions about this all the time. Hopefully this will save someone a lot of time.

  • Related