I recently answered a question on a forum which I believe will be useful to many of the readers in the audience.
SSIS packages are widely used for data import from and export to files. One of the main tasks in this situation would be to check if files with certain kinds of names exist in a particular folder or directory, i.e. basically perform a wild-card search in a directory.
The solution
This can be achieved by using the EnumerateFiles() method of the System.IO.Directory class in the SSIS Script task. Here’s the sample package:
In a folder, I have a set of files, some with similar names (which we will search from the SSIS package).
The SSIS package has two (2) variables:
| Variable Name | Configuration on the Script Task | Description |
| SearchPath | ReadOnly, Input | This is the path to be searched |
| SearchPattern | ReadOnly, Input | Pattern to be searched |
| FileExists | ReadWrite, Output | A boolean indicating downstream processes whether files were found or not |
The script is a quite simple implementation as below:
public void Main()
{
// SQLTwins: SSIS: Blog #0421
string searchPath = Dts.Variables["User::SearchPath"].Value.ToString();
string searchPattern = Dts.Variables["User::SearchPattern"].Value.ToString();
System.Collections.Generic.List<string> searchResults = System.IO.Directory.EnumerateFileSystemEntries(searchPath, searchPattern).ToList();
if (searchResults.Any())
{
Dts.Variables["User::FileExists"].Value = true;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
Ensure that you have the following directive in the “namespaces” section of your script:
using System.Linq;
Here’s the script in action:
As you can see, the script can help perform a wild-card search in a given folder or directory.
Further Reading:
- File System errors when trying to move and rename a file [Blog Link]
- Adding Date & Time to file names after processing [Blog Link]
- VSTA Errors when working with SSIS packages [Blog Link]
- System.IO.Directory.EnumerateFiles [MSDN Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 