April 19, 2010 at 10:06 am
I'm trying to figure out the best way to return a file name to a package to use as a source. I have a vendor who cannot send me a daily file with just the date in the file. There is the date plus the time plus some random. The pattern is always the same, but the last 10 characters vary.
The files are in a directory - it is pretty deeply nested so I use a variable for the UNC, then the rest of the pattern us "File_MMDDYY" (yesterday's date - the below expression returns that) then 10 UNKNOWN characters + .csv.
So far I have the below to get all except the last 10 charcters of the file name. return the 10 characters or use a wild card to get the one file name into a connection string?
(@[User::FlatFilePath])+"File_"+(MONTH(DATEADD("dd", -1, GETDATE())) < 10 ? "0" : "")+(DT_WSTR, 2)MONTH(DATEADD("dd",-1,GETDATE()))+
(DAY(DATEADD("dd", -1, GETDATE())) < 10 ? "0" : "")+(DT_WSTR, 2)DAY(DATEADD("dd",-1,GETDATE()))+SUBSTRING((DT_WSTR,4)YEAR(DATEADD("dd",-1,GETDATE())),3,2)
April 19, 2010 at 10:10 am
You can use the For Each loop container to loop through a group of files. Set the For Each loop up so that it only considers files that meet the naming convention that you've shown. Even if there should only be 1 file, the For Each loop container setup will allow you to find that file easily.
April 19, 2010 at 10:29 am
Thanks for the quick reply, John. I"m trying to figure out how you mean.
In the ForEach Loop, would I use a ForEachFileEnumerator? How do I get the collection of files that match my expression (or rather the one file). The directory contains files for several days, but I only want to return the file for yesterday's date.
April 19, 2010 at 10:50 am
Yes, use the Foreach File Enumerator. Then, use the Expressions editor to set the FileSpec Expression to your expression from your first post. This will tell the enumerator to only consider files in the directory that have that pattern.
April 19, 2010 at 11:31 am
OK so I set up the foreach loop and put the (file >> table) data flow task in it. It is not finding the file using the path expresssion in the Directory expression (Collection) and the string expression for FileSpec (Name only). That value is in another variable now called @varDateFileString. Is there some wild card character or something? Should I say @varFileDateString + "*"
April 19, 2010 at 11:46 am
Nevermind. Answered my own question.
Solution Details:
1. Looking for a file with yesterday's date in the file name
2. pattern is "fileName_" + ddmmyy + <ten random chars> + .csv
3. Path is in a user string variable @varFilePath
4. The first part of the pattern is in a user string variable @varFileDateString. That is an expression <
"fileName_"+(MONTH(DATEADD("dd", -1, GETDATE())) < 10 ? "0" : "")+(DT_WSTR, 2)MONTH(DATEADD("dd",-1,GETDATDATE()))+SUBSTRING((DT_WSTR,4)YEAR(DATEADD("dd",-1,GETDATE())),3,2)
>
5. To get the file name (or names), I set up a For Each Loop. In the Collections Property, set to Foreach File Enumerator.
Expressions - Directory @[User::varFilePath]
FileSpec@[User::varFileDateString] + "*"+".csv" (select name and extension radio button).
6. Put the data flow task inside the for each loop.
Thanks for the help, John!
April 19, 2010 at 11:50 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply