Reading a Filename which has a Wildcard

Hi - I need to read a file but I do not know the whole filename!

For example, I need to read a file whose name is:- Finance_200510.xls

I know the prefix is "Finance" and I know the suffix is ".xls", but the bit in the middle could be anything. In the above example, it is a date which will vary from month to month.

I could achieve this as follows:-

1. I already need a .CMD file which contains a DTEXEC command.
2. So, I could insert the following line before the DTEXEC command:-
DIR Finance*.xls > ListFile.txt
(Alternatively, this command could be invoked from within the Package)
3. I could read the file: ListFile.txt using a SSIS Script Component
and extract the first occurrence of the string starting with "Finance"
and ending with ".xls" and save the filename to a package variable: @.[user::filename]
4. The package variable @.[user::filename] could be used in the expression property of a Connection Manager
5. The Connection Manager could then read the file.
6. Optionally, I could generate a RENAME command using a Script Component to rename Finance_200510.xls to Finance_200510.done so that it is not re-processed.

Can anybody suggest a simpler solution, please?!

Thanks.You can use multiFlatfile connection to provide file name with wildcard. In your case, you will have to make sure that there are no other unwanted files in that folder which might match the wildcard you are using. This will eliminate the need for listing all the files in the folder, and use script component to get the file whose name starts with "Finance". In your multiflatfile connection, you can just use Finance*.xls, and it will catch your needed file.

See BOL topic
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/31fc3f7a-d323-44f5-a907-1fa3de66631a.htm
for more on Multiple Flat File Connection Manager. The ideal use for Multiple flat file connection manager is to process more than one file which are of the same format (instead of using For Each Loop), but it will also solve your purpose of choosing file with Name that matches a wildcard.

HTH,
Ranjeeta Nanda|||Or similarly, you could use the Foreach Loop with the For Each File enumerator and a file pattern of "Finance*.xls."

If you really really wanted the exact name of the file before you started processing, or needed to check for other conditions, you could use the System.IO namespace within a Script Task to retrieve files that match the pattern, make sure there's only one, and pop the filename into a package variable for use by the next task.

-Doug