July 5, 2018 at 12:07 pm
Well, after two days of digging and pulling hair I am posting here.. geeeeeeeeeesh.. Anyway on with my issue.
I have a simple SSIS job that truncates a table, reads an excel file on a share and dumps that file into a sql db. Simple right? hmmm.. Here is where it gets tangly..
I setup the job to run using a proxy. This proxy has proper privileges etc on the shared drive (but no access to the sql server). When I try and run this it fails, no errors, nothing in the SSIS catalog reporting.. I mean nothing.. I ran this job as myself as the proxy (I have full rights to the SQL Server) and it works fine. So just for laughs I added the failing proxy account to local server admin roles just to see and voila it works. So I have come to conclusion that its trying to access a folder or temp location it doesn't have access to. I allowed the proxy account access to the sqlagent appdata temp location but it still failed. I ran MS process monitor to see if I could see what file my proxy account was failing on but still nothing. I am at a complete loss as to why this will not work under this proxy account. BTW a big piece of this is that this proxy account is used in 100's of other jobs and it works fine. The only difference is that this job is pulling an excel file which none of the others are doing. Anyone have any ideas or help it would be fantastic..
July 8, 2018 at 8:54 am
tchaytor - Thursday, July 5, 2018 12:07 PMWell, after two days of digging and pulling hair I am posting here.. geeeeeeeeeesh.. Anyway on with my issue.I have a simple SSIS job that truncates a table, reads an excel file on a share and dumps that file into a sql db. Simple right? hmmm.. Here is where it gets tangly..
I setup the job to run using a proxy. This proxy has proper privileges etc on the shared drive (but no access to the sql server). When I try and run this it fails, no errors, nothing in the SSIS catalog reporting.. I mean nothing.. I ran this job as myself as the proxy (I have full rights to the SQL Server) and it works fine. So just for laughs I added the failing proxy account to local server admin roles just to see and voila it works. So I have come to conclusion that its trying to access a folder or temp location it doesn't have access to. I allowed the proxy account access to the sqlagent appdata temp location but it still failed. I ran MS process monitor to see if I could see what file my proxy account was failing on but still nothing. I am at a complete loss as to why this will not work under this proxy account. BTW a big piece of this is that this proxy account is used in 100's of other jobs and it works fine. The only difference is that this job is pulling an excel file which none of the others are doing. Anyone have any ideas or help it would be fantastic..
Two questions, first of all, what are the Windows permissions granted for the source folder and secondly, is it possible that the Excel file was open at the time of the execution?
😎
July 9, 2018 at 5:45 am
Permissions on source is full control and no the files were not open.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply