January 15, 2010 at 8:40 am
I need to check a directory hourly, and pickup any named files one at a time with .csv extension. Each file needs to be renamed. Each file needs to have its data populated into a table. Each file needs to be archived into another directory.
I was able to construct the data flow element to fill successfully the table. I was able to use a File System Task editor to copy a directory or copy a file.
However, I could not configure it (with or without a variable) to copy a file based on an extension only regardless of the name of the file. I could not get it to recognize the wildcard symbol =*.
The variable as string did not allow the wildcard * to be recognized as a wildcard, but as text.
How do I copy a file based solely on the extension (.csv)?
I constructed a ‘foreach loop container’ to pick up a file one at a time. I configured it with:
Enumerator=for each file enumerator
Folder=path
Files=*.csv
Retrieve file name= Name and extension
The foreach loop container did not seem to recognize my use of the wildcard = *.
I’m sure this has been done many times before. In summary, I need this SSIS package to cycle through a directory, picking up each file one at a time with the .csv extension; rename it; process it through data flow element, and then move it to another directory in order to archive it.
Thank you. All help is appreciated!:-P
January 15, 2010 at 12:55 pm
Does it work if you specify a single file that you know exists without the wildcarding?
I have done this successfully with the ? wildcard.
January 15, 2010 at 1:34 pm
Yes, I can copy a file without the * wildcard. Is there another syntax for wildcards? It's unknown how long the name of the file would be, for example ?????.csv wouldn't work for larger names, if I follow what you are saying.
January 15, 2010 at 1:49 pm
If the file has a patter like revenue_<<somesortoftimestamp>> you can use the revenue_*.txt pattern. This has always worked for me. And as long as the file name starts with revenue_, it doesn't matter how long the file name is.
January 15, 2010 at 2:01 pm
[font="Comic Sans MS"]
wildcard *.csv should work.
Please have a look at the link below and see if you are doing something wrong.
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
January 15, 2010 at 3:22 pm
The ? wildcard is if you know the exact number of characters.
In my working example I use Myfile.???????? where the extension is a YYYYMMDD date format.
I also use Retrieve file name= Name and extension as you are trying.
The referenced article uses fully qualified. I migh try experimenting with the Retrieve file Name setting.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply