CODEWITHSUNDEEP
sql-serverssis
Lock
Lock

Reputation: 5522

Why does my SSIS package fail with filesystem permission error when run from the Integration Services Catalog

I have an SSIS package that accesses a folder on a network share. If I run this package as part of a SQL Agent Job, it succeeds. If I run this package directly from the SSIS Integration Catalog (Right Click and execute) on my development PC, it fails with a permission error . If I run this package directly from the SSIS Integration Catalog (Right Click and execute) on the server hosting the SQL instance, it works.

All users's involved in this process have access to the folder (my username, the user setup to run the SSIS service and the SQL Agent service). I know this for sure because I can accsess the folders logged in under any of these users.

Any ideas why this would be?

What baffles me is that it works if you run it from the server hosting the SQL instance, but not from my local PC (when logged in under the same user)!

We've gotten to the point that when we want run certain packages that access the file system, we create an SQL agent job to run it. It's not ideal.

Upvotes: 0

Views: 2457

Answers (2)

Paras Sharma
Paras Sharma

Reputation: 55

SQL Server Management Studio does not provide execute permission on Integration Service catalog using 'Sql server Authentication'. So, you can not execute any package from your machine.

you are able to run your package on the server machine because SQL server instance is installed on that particular machine and you are connecting SQL server by 'Windows Authentication'.

I hope this could help you!

Upvotes: 0

Lock
Lock

Reputation: 5522

Adding the NETWORK SERVICE user account to the folder fixed the issue. I'm not entirely sure why as it runs under the context of the user executing the package, but it fixed the issue.

Upvotes: 1

Related Questions