Error when adding SSIS package configuration in SQL Agent job

0
votes

I'm having trouble setting up SQL Server Agent to be able to execute SSIS packages using configuration files. We have SSIS 2012 and SQL Server 2014 SP1 if it matters.

Packages are stored in the file system on our DB server. After clicking on the Configurations tab in the job setup, I was successfully able to add a .dtsConfig file found in the default package store path: C:\Program Files\Microsoft SQL Server\120\DTS\Packages.

However, upon clicking OK to save, I got the following error:

"Access to path C:\Program Files\Microsoft SQL Server\120\DTS\Packages\PackageName.dtsx is denied. (mscorlib)"

Firstly, I absolutely have permission to that path, as does our SQL Agent account, and the proxy account used to run the package via the Agent. Secondly, why would it error about access to the package on the Configuration tab, not access to the configuration file?

The packages in question DO execute when I run the job, successfully, but appear to do absolutely nothing. My thought is that it's probably not using the configuration file (since it thinks access to something is denied), and therefore is not looking for the correct network path. Anyone else run into this?

2
sql-serverssisetlssis-2012
I don't have a solution for you, but when I moved to SQL Server 2012 I changed to the project deployment model which gives many benefits over the package deployment (which uses .dtsConfig) model. Are you setting up your jobs from the actual SQL server or from a client? – Nick.McDermaid
It gives the error on both my desktop and when I remote into the server. And thanks for the suggestion, but I'd prefer not to use project deployment, as that's unnecessarily complicated for our environment---these aren't true ETL packages, rather, they just take data from a spreadsheet or flat file and load it into a database that one of our main applications uses (it's not a warehouse). In other words, they're stand-alone packages, so don't really need to be put in projects. – Dillon P

2 Answers

0
votes

Permissions don't always cascade to child folders. Check the subfolders and confirm permissions exist there.

Even though your proxy account has "FULL" control to the bottom of folder chain it can deny access to child folders.

0
votes

It turns out that this error was not the cause of the package failing to pick up files. The package configuration file was missing a variable for the collection folder in a For Each loop. So, even though it was giving an error, it actually was using the package configuration. False alarm!