1

I have a package I created that runs fine in Visual studio.
The package uses a for each file loop and the variable points to a folder like this:

C:\Users\Desktop\Folder\

However, once I deploy the package on SSMS, when I execute the package I get the following error:

Foreach File in 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

Why do I get this warning in SSMS, but in VS it runs fine?

Note: The SQL Server is not hosted on my local machine.

AlanPear
  • 737
  • 1
  • 11
  • 32

1 Answers1

4

If you use a local path to the folder, like this

C:\Users\Desktop\Folder\

Then when you run the package on the SQL Server, the files need to be in that LOCAL PATH on the SQL Server.

When you run it in Visual Studio, it looks for the files in the local path on your machine.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • That would make sense. So when I deploy a package on my SQL Server, the scope of that package is now within the installation of the SQL Server? If I wanted to have my package run elsewhere it would need to be on another machine that is always on to run the package? – AlanPear Feb 08 '18 at 21:04
  • Yes, exactly. If you wanted to get the files from a machine other than the SQL Server, you would need to share the folder, make sure the SQL User has permissions, and use the UNC path instead of a Local Path. – Tab Alleman Feb 08 '18 at 21:08
  • Thank you very much for explaining this. SSIS has been difficult to learn. – AlanPear Feb 08 '18 at 21:27
  • How would I get this package to pull from a client machine dynamically? I've tried to look this up but I can not seem to get a clear answer. – AlanPear Feb 11 '18 at 18:52
  • Use a package variable for the path. If you've tried this and gotten an error, you should create a new question and post what you did and the error you got. – Tab Alleman Feb 12 '18 at 14:36
  • I'll give it a try. I don't see how that would be any different from setting the folderpath string statically. Won't they be exactly the same? – AlanPear Feb 13 '18 at 01:31
  • Maybe I'm confused by your question..you asked how to do it dynamically. – Tab Alleman Feb 13 '18 at 16:24
  • Perhaps dynamically isn't the right word. When I execute the package from SSMS, how can I get the server to read a filepath on a client machine, rather than its own. – AlanPear Feb 13 '18 at 16:58
  • Ah, then you do use a variable for the path, and post 2012, you can set the variable on the client machine with a parameter. Pre-2012 you can set it with a config file. – Tab Alleman Feb 13 '18 at 19:09
  • But there's no way for me to execute the package from SSMS, and grab a file from a client machine? – AlanPear Feb 13 '18 at 20:04
  • Each time I think I know what you're asking, you seem to be asking something else. Maybe you should create a new question and explain it in detail. Lots of detail. – Tab Alleman Feb 14 '18 at 15:00
  • Lol. It's something I need to work on. I actually did post a semi-related question here: https://stackoverflow.com/questions/48723921/how-to-handle-automation-of-flat-files-in-sql-server? – AlanPear Feb 14 '18 at 17:45