July 7, 2016 at 4:14 am
Hi there,
I have a SSIS 2014 package using the Project Deployment model and parameters. I have a project parameter called FlatFilePath which is the path to a folder where a bunch of text files are. I want to iterate this folder and load the files into SQL Server.
The Foreach Loop container with File Enumerator does not allow you to set the Variable in the Variable Mappings tab to a Project Parameter, only package variables. So I am using a user package variable called User::FullFilePath to store the output of the Foreach Loop container. This means I cannot use a Project Connection Manager for the flat file source because that only allows project parameters in the Expressions property for the connection string. I am therefore using a package flat file connection manager.
This is fine when running the package in SSDT, however when I deploy it to my server and run it, the output of the Foreach Loop container is Empty and therefore cannot find any files. I see this message in the log:
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 was empty.
Has anyone else experienced this and discovered a workaround?
July 7, 2016 at 5:45 am
The folder to be iterated over can be a package or project parameter. Can you confirm that you are using UNC naming conventions throughout (and not drive letters)?
The file names themselves will change at run time and, therefore, cannot be parameters (parameters are read-only during package execution).
The file name can be assigned to a variable within the FEL.
The variable can then be used in a data flow which processes the file.
From you description, it's not totally clear where things are going wrong in all of this, can you elaborate?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 7, 2016 at 3:15 pm
The folder I am iterating over is a project parameter and works fine (and is a UNC path that is permissioned to my user account and the SQL Server Agent Service).
The enumerator assigns the package variable with the file name to load on each iteration when executed in SSDT fine. However, when deployed to the server, the Foreach Loop enumerator does not find any files, with the "enumerator is empty" message. I'm not sure why this would be.
July 7, 2016 at 3:30 pm
keymoo (7/7/2016)
The folder I am iterating over is a project parameter and works fine (and is a UNC path that is permissioned to my user account and the SQL Server Agent Service).The enumerator assigns the package variable with the file name to load on each iteration when executed in SSDT fine. However, when deployed to the server, the Foreach Loop enumerator does not find any files, with the "enumerator is empty" message. I'm not sure why this would be.
OK, that's getting puzzling. Check the All Executions report and verify that the parameters have their correct values and that everything else is looking right.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 7, 2016 at 3:39 pm
The All Executions report, overview page looks fine. Parameters Used list looks good and the UNC path name to the flat file is correct.
July 7, 2016 at 4:02 pm
keymoo (7/7/2016)
The All Executions report, overview page looks fine. Parameters Used list looks good and the UNC path name to the flat file is correct.
Just to totally rule out a permissions issue, can you temporarily change the SQL Agent logon credentials to be yours, rerun the job & then verify (again from the All Executions report) that the job was, in fact, run with your credentials.
If that still fails, I am out of ideas.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 8, 2016 at 4:19 am
Phil Parkin (7/7/2016)
Just to totally rule out a permissions issue, can you temporarily change the SQL Agent logon credentials to be yours, rerun the job & then verify (again from the All Executions report) that the job was, in fact, run with your credentials.
Good one! It was a permissions issue. The share permissions were correct, but the NTFS permissions to the folder were locked down and SQL Agent didn't have permission. I've changed it now and all is good.
What annoys me about this, is that I didn't get an Access Denied error bubbling up from the OS. Grrrr. Thanks for your help.
July 8, 2016 at 8:30 am
keymoo (7/8/2016)
Phil Parkin (7/7/2016)
Just to totally rule out a permissions issue, can you temporarily change the SQL Agent logon credentials to be yours, rerun the job & then verify (again from the All Executions report) that the job was, in fact, run with your credentials.
Good one! It was a permissions issue. The share permissions were correct, but the NTFS permissions to the folder were locked down and SQL Agent didn't have permission. I've changed it now and all is good.
What annoys me about this, is that I didn't get an Access Denied error bubbling up from the OS. Grrrr. Thanks for your help.
Great that you tracked it down in the end. You are right to be annoyed about the lack of errors – and I assure you that you are not alone in being annoyed about it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 10, 2016 at 11:58 am
Mine is relatively close to this one.
My process deletes files from archive after x days.
I hit the loop and get the same complaint, but I've previously
moved 1 file to the archive which meets the criteria.
How can I find out what is being done internally, i.e. what's the
path after SSIS does it's thing ? What files does it think it's looking for ?
Thanks
October 10, 2016 at 12:16 pm
rsampson (10/10/2016)
Mine is relatively close to this one.My process deletes files from archive after x days.
I hit the loop and get the same complaint, but I've previously
moved 1 file to the archive which meets the criteria.
How can I find out what is being done internally, i.e. what's the
path after SSIS does it's thing ? What files does it think it's looking for ?
Thanks
There is no option to 'find out what is being done internally'. It's very difficult to help you at all without more information. I suggest that you start a new thread.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 21, 2018 at 11:12 pm
keymoo - Friday, July 8, 2016 4:19 AMPhil Parkin (7/7/2016)Just to totally rule out a permissions issue, can you temporarily change the SQL Agent logon credentials to be yours, rerun the job & then verify (again from the All Executions report) that the job was, in fact, run with your credentials.
Good one! It was a permissions issue. The share permissions were correct, but the NTFS permissions to the folder were locked down and SQL Agent didn't have permission. I've changed it now and all is good.What annoys me about this, is that I didn't get an Access Denied error bubbling up from the OS. Grrrr. Thanks for your help.
In my case, i had to change the sharing permission for the shared folder to include NT Service\sqlserveragent with read/write access to allow agent job to allow the folder. Offcourse it needs to be added to the security. Thanks for the info it really helps!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply