1

I've gone through a number of posts - and haven't come across a solution to this. I've spent days on this... I have a project which contains one package with parameters that as part of the processing, moves files from file server to db server & vice versa. It runs fine on ssdt. I deployed it to SQL Server 2012 ssisdb using project deployment. Before doing this, I mapped parameters to configuration values on the ssdt side, and it appears that I can use these on the server, so saw no need to set up environment variables for (yet). When I execute the package via ssms on the server, I get 'Access to the path ... is denied'. My understanding is that the package will execute using my credentials (since I'm using mine on ssms) -- and, as I said previously, this was working fine with my credentials on ssdt. I've checked permissions on the file and the share. I've confirmed that I am added to the Dcomcnfg - security - Launch and Activation Permissions.

user3460848
  • 63
  • 1
  • 7
  • Is it setup as a SQL job or not? as you said, I suspect it is using a different login like sql server admin account that doesn't have access. – Holmes IV Mar 03 '16 at 23:27
  • This is not set up as a job. I'm just trying to run it from the catalog. I've also made sure the sql server admin accounts had proper file permissions, although it seems clear that my credentials are being used. – user3460848 Mar 04 '16 at 14:27

2 Answers2

2

When I re-started ssms this morning as administrator, the package ran successfully without the 'Access to the path ... is denied' error . I confirmed that I would get the error otherwise.

user3460848
  • 63
  • 1
  • 7
  • As an aside, if I executed the package on my machine (vs. the server), - even as administrator, I would get 'The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty'. I'm surmising that this might be due to trying to run the package outside the server - even though I was doing this through a connection to the server via ssms. If someone cares to comment on this, I'd be interested in yays or nays. – user3460848 Mar 04 '16 at 19:03
2

Realise this is super old thread; but I've been running into this issue all day today. The SSMS history error log states that the job is being run as "NT Service\SQLSERVERAGENT" (at least in my case). I've added SQLSERVERAGENT to local admins group and rebooted server (I'm sure just retsarting the SQL Server Agent would have worked too).

GOZ_91
  • 21
  • 2