October 31, 2018 at 9:50 am
I have 3 packages that run by a Proxy account who has all the permissions to access the files in the folder and also to the server itself. The package executes successfully when we run from visual studio and picks the files. However, when we run as a server agent it doesn't pull any files from my local machine but it successfully runs.
I looked in SSIDB to inspect and I found this warning :
"Foreach File - Check Error Folder:Warning: 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"
We have "Foreach loop" and I checked the paths are directed to the correct folder as well. I don't know what I should be checking now. Please advice
I also created a script package to check if the file exists in the folder path . When it runs it shows that there are no files but there are files in it. It is so weird I spend all day yesterday figuring this out.
Thanks
October 31, 2018 at 10:01 am
The only thing I can think of is that, despite the proxy account, the file enumeration is being performed in the context of the SQL Server Agent account. Might be worth giving the latter access to the folders just to test whether or not that's the case.
John
October 31, 2018 at 10:37 am
Hi,
Thanks so much for your reply. So you mean I should try giving SQL Server Agent Account access to the folder? I know its too much to ask but since I have never done that, can you tell me how I can do it?
Much appreciated!!
October 31, 2018 at 10:42 am
Well, you said that the "Proxy account ... has all the permissions to access the files in the folder". Just do the same thing for the SQL Server Agent account as you did for the proxy account. If it wasn't you that did it, ask your server admin to show you how to grant NTFS and share-level permissions.
John
October 31, 2018 at 11:19 am
oh ok got you!! I guess my question was how can I grant permissions for the whole service (SQL SERVICE AGENT SERVICE ACCOUNT)? But. . .
I checked and the login account for the Sql Server Agent has access to the folders as well.
October 31, 2018 at 11:25 am
My question to you would be, is the SQL Server instance that you're trying to run this package on, and the system that you've been using Visual Studio to develop the package, the same system or are they two different systems?
From some of your comments, I'm thinking they're two different systems, in which case you need to change the location it's trying to pick up the files from.
October 31, 2018 at 11:52 am
I guess I have the files in the same system because we save files on the network\\directory \folder\files . For instance, if I load the file in my local directory folder, it also saves the file in my remote system same folder and they both map to the same path.
I am running packages from visual studio and sql server agent from the same remote server on the same machine. I hope it makes sense and I hope I understood this correctly.
October 31, 2018 at 2:17 pm
v-afbhur - Wednesday, October 31, 2018 11:52 AMI guess I have the files in the same system because we save files on the network\\directory \folder\files . For instance, if I load the file in my local directory folder, it also saves the file in my remote system same folder and they both map to the same path.I am running packages from visual studio and sql server agent from the same remote server on the same machine. I hope it makes sense and I hope I understood this correctly.
It may make sense, but lets try to clarify a bit here...
First. The location that the files you're trying to access, in your first post you stated "it doesn't pull any files from my local machine but it successfully runs." To me, this indicates that your local PC (where VS {Visual Studio} is installed) has a folder, lets call it C:\files, which also exists as a folder on the server (which is NOT your local PC.) Therefore, there'd be no files in this location, and thus nothing for the package to access. Unless the location is a network share, accessed via a UNC from both the VS PC and the server (ex: \\networkserver\sharename) But then, depending on the behavior of your package, once you run it locally, if it "cleans up" the folder by removing the files, again, there'd be nothing for the server to access.
Second. based on your comment "I am running packages from visual studio and sql server agent from the same remote server on the same machine" I would read that as you're Remote Desktoping into the server where SQL Server is installed and that Visual Studio is also installed there (which I would find highly unusual myself.) What I would expect to be more likely is, you have your workstation, with VS installed, and are using either VS or SSMS to access the SQL Server instance on another system. Obviously, I could be wrong on this, but I'm betting I'm not and it's easy to check. Check the name of your PC and the SQL Server name. My general method would be, on my PC open a command prompt and type in "echo %computername%" and hit enter (no quote marks) then in SSMS do "select @@servername" (again, no quotes.) I'll bet a nickel the two don't return the same name.
October 31, 2018 at 4:09 pm
Right, so I just checked and you are right, they both don't return the same name.
But how will this help? Does that mean that these are two different systems? Will it help if I download Visual studio on my local machine and run the package from there and also run the job from ssms on my local machine?
Also, Like you said "Unless the location is a network share, accessed via a UNC from both the VS PC and the server (ex: \\networkserver\sharename) " this could be what we are doing. We have our files on \\matdrive(its on the network)\folder\files.
October 31, 2018 at 4:45 pm
v-afbhur - Wednesday, October 31, 2018 4:09 PMRight, so I just checked and you are right, they both don't return the same name.But how will this help? Does that mean that these are two different systems? Will it help if I download Visual studio on my local machine and run the package from there and also run the job from ssms on my local machine?
Also, Like you said "Unless the location is a network share, accessed via a UNC from both the VS PC and the server (ex: \\networkserver\sharename) " this could be what we are doing. We have our files on \\matdrive(its on the network)\folder\files.
Actually, knowing this helps a great deal.
Yes, you are on two different systems, which is fairly typical, and having the files in a network share also simplifies things.
When you've been running the package in VS, you've *been* running it on your local PC.
Here's what I'd try first and foremost, going from the message you posted originally. Put a file or files in the network share, then set the package to run through the SQL Agent on the remote server, sit back, and wait for it to run. Or, you could manually run it through SSMS on the server (right click your Agent job and run from there.)
If it still throws the message above, about no files, then possibly your development PC and the server are pointing to different shares, although this is unlikely.
January 25, 2019 at 2:26 pm
Hi,
I am back after forever . I am sorry I was pulled in another project. OK so I tried what you said.
I placed a file on my network drive and opened ssms from my local machine and ran the job as SQL server agent who has all permissions to access the folders etc. The files still did not get picked up from my network drive but the job ran successfully. Please see the warning message I see in integration catalog.
what should be my next move guys? I am so confused
Appreciate any help and advice.
January 28, 2019 at 1:55 am
The error message explains what the problem is - it isn't finding any files. I don't know whether that's because there aren't any files that match the pattern, or whether the permissions still aren't right. Does the file get picked up if you run the package from Visual Studio under your own account.
Are you sure the SQL Server Agent has permissions on the folders? Does SQL Server Agent run under a domain account? Is the folder local, or on a separate computer?
John
January 28, 2019 at 9:04 am
Yes, the file gets picked up when I run the package from Visual Studio manually.
The files are placed in the folder on the shared network drive. I wrote a script to see if the file is present in that location. When I run the package it shows the file isn't present in that location even though there are files in that location.
yes, SQL server agent has access to all folders and I also created a proxy account with all permissions given but still it doesn't pick up any files.
I don't know if the folder is on separate computer. I mean the folder is on network drive.
I also tried putting the files on my desktop and ran the (Check_for_files) script package by changing the location to "Desktop" it still doesn't detect the files.
Is there anything I can do ??
January 28, 2019 at 9:13 am
Put an extra task in your package to run whoami and put the result in a text file or a table. That will tell you under what security context the package is running. Then check your permissions again to make sure that that account has access. Have you checked permissions at share level and NTFS level?
John
January 28, 2019 at 9:52 am
So do I add an execute task for checking whoami or a dataflow task? I am sorry I haven't done this before so not sure how to check. I am sure its pretty simple. Let me check the permissions again at both share level and NTFS level.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply