1

This issue was addressed in a prior post, and I have a very similar situation, however it is sufficiently different that a new post is justified.

I have an SSIS package with a For Each file loop. In Visual Studio 2017, the package behaves exactly as expected in debug mode. However, once I deploy the package to my SQL server and run it from there, I receive "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 is empty." The package itself exits with success, this error logged as a warning, and suffice to say, my target table remains empty.

Unlike the previous poster experiencing this issue, I have been using a UNC path for my source folder (values genericized):

ForEach Loop Editor

I have validated that the SQL Account and SQL server itself have rights to my target share and files. I have changed the Integration Services service to use a known good domain account. I do not see any access denied errors, etc. What am I missing?

stinkyP
  • 555
  • 6
  • 14
  • This might be nothing, or it may solve your issue, but I notice that you don't have a "\" at the end of your folder path. I've run into issues before where the ending \ wasn't explicitly added to the folder path and SSIS was unable to find the folder. You might try adding one to the end of your folder path to see if that makes any difference. – digital.aaron May 17 '18 at 22:35
  • Still smells like a permissions issue to me. Also, something about administrative shares is (share$ vs share) behaving differently with new(er) version of windows but I can't recall what the issue is – billinkc May 18 '18 at 02:53
  • What account is running the job? The SQL account does not matter if you are executing the agent job as a different user. That user needs the permissions. – Brad May 18 '18 at 15:35
  • digital aaron, I was so hoping that would be it! But no. – stinkyP May 19 '18 at 00:21
  • Billinkc, I created the share as a visible share, no difference unfortunately – stinkyP May 19 '18 at 00:22
  • I assume the Integration Services Service? I have configured that service to use a domain account that has explicit permissions on the share. It is the same account running the SQL Server Agent service for the instance. It just kills me that it works in dev but not in production. It is certainly true that the security context is different in each scenario, but I am assuming I would get an "Access Denied" error rather than a "For Each Enumerator is empty"? – stinkyP May 19 '18 at 00:27
  • I have also tried simplifying the share as \\Server\TargetFolder rather than \\Server\Share\TargetFolder... no luck – stinkyP May 19 '18 at 00:32
  • Just for grins, I granted the everyone group full access to the relevant directories, no change in outcome. :-| – stinkyP May 22 '18 at 22:16

2 Answers2

1

UNBELIEVABLE. Here is the answer. While I can run the SSIS package in dev from VS2017 on my workstation, once published to the SQL server, the job fails if I execute it from SSMS on my local workstation. However, the job succeeds if I run it from SSMS on the SQL server itself. Same domain, same accounts, same DNS. Ughh. I sure hope this saves someone a few gray hairs.

stinkyP
  • 555
  • 6
  • 14
  • That'll be a [Kerberos Double Hop](https://stackoverflow.com/questions/14928350/how-can-i-fix-the-kerberos-double-hop-issue) issue – billinkc Nov 16 '18 at 00:39
0

To people running into this issue you can create a job under SSIS SQL Server Agent and run it as SSIS Executor.

Execute directly under Integration Services Catalog might not be enough.

Check image: SSIS Job

fly
  • 39
  • 1
  • 7