Mind-boggling Problem!

I created an SSIS package that reads a comma delimited (CVS) file from a network share, loads it into a SQL Server table and then moves the file to a folder called Completed. Simple, right? After fighting my battle with the "truncate" error that you could see in my previous post the package runs fine. So it's time to set it up as a SQL job.

I set up the job, run it and it says success but there is no data in the database and the file has not been moved to the Completed folder.

I added logging to the package so that everything will be logged onError, onInformation, and onWarning to the Windows Event log. (You can do that by going to the SSIS>Logging menu item.)

I see that I have a Warning in the event log that the job could not find any files. I check on the permissions and I see that everything is set up right. Both the share permissions and the Security tab shows Modify permissions for the group that the SQL Agent user is in.

I spend all morning with a co-worker of mine (thanks Dennis for your time!) trying all kinds of crazy workarounds and solutions. And what we found that worked is mind-boggling!

If we change the permissions on the share to give Modify rights to the SQL Agent user itself (not just the group it's in) everything works!!!!

Can someone please explain to me why?????