SSIS For each Loop not finding files when deployed

Question

Post reply

SSIS For each Loop not finding files when deployed

david.roper

SSC Enthusiast

Points: 174
More actions
November 23, 2019 at 3:37 pm

#3700255

Hi,
I have an issue with a SSIS package deployed to the catalog. This package includes a For Each container that scans a folder for CSV files. I can run the package from Visual Studio on my development box using my credentials and it completes successfully finds the files targeted by the for each loop and processes them as it is supposed to. With the package deployed to the Integration Services Catalog however it does not work and I get back the warning; The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.
In both cases I have used the UNC paths. In both cases it is running with my credentials.
The issue seems similar to this post here;
https://www.sqlservercentral.com/forums/topic/ssis-package-foreach-loop-container-not-finding-files-when-deployed-fine-in-dev
But they were able to resolve this by changing the folder permissions for the account executing the package. Given I am running this with the same account in both places and it is pointing at the same directories it seems to be something else.
I am working with:
Visual Studio 2017
SQL Server 2016
I am executing the package from the catalog by manually executing it.
If anyone knows the answer or has a good idea for how to diagnose it would be much appreciated! I can think of things I could try but I am not very convinced they will actually get me where I need to go.
February 24, 2020 at 5:31 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

Did you actually try the solution you found? I ask because it may be that the package isn't actually running as you as you think. I'm not an SSIS kind of guy but I've had similar issues in other places where what you think is happening simply isn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GaryV Hall of Fame Points: 3307 More actions · Answer 3

I'm guessing, even though you say it has the same account and permissions, it has something to do with your connection manager definitions.

That could be due to rights issues - the account that runs the job doesn't have access to the files.

Or maybe the server where the job is running doesn't have a connection to the location of the files.

david.roper SSC Enthusiast Points: 174 More actions · Answer 4

Hi, thanks for people's comments. Yes I am revisiting the permissions or more accurately trying to get my infrastructure colleagues to do that, it's hard work! Trying different configurations out now to see what different results I get.

Is there some additional logging I can add that might help with this?

david.roper SSC Enthusiast Points: 174 More actions · Answer 5

Just to let people know I did fix this in the end by changing the enumerator to pick up text from the file name rather than text that exists in the file extension. It seems the extension had become invisible to the account. No idea why it happened and infrastructure department deny making any changes, although that seems the only explanation. No more help required although if anyone has a theory as to why this occurred I would be interested to know.