I’ve developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it off from my PC will throw a warning saying the For Each File enumerator is empty. My account and the SQL SQL service account both have access to the file share where the SSIS package is pointing. Anyone else encountered a similar issue?
The SSIS package will execute successfully if executed remotely through a SQL agent job.
I would agree with the comment from Travis Page. Having just gone through this is sounds like either the Service Principal Names(SPN) are missing for the SQL Service or the file share. Ultimately you will need the “MSSQL” SPN for your SQL Server instances and the “HOST” SPN for host of the fileshare.
Some reading/blog articles to help understand troubleshoot any kerberos issues:
This blog describes the creation of SPN’s using the SETSPN command line tool:
There is a tool published from Microsoft which will help determine the SPN’s require for SQL Server. This blog talks about the tool:
Additionally there is this below article from Microsoft which will help troubleshoot any kerberos problems: