How can I detect if there is any file in a folder using SSIS?

Question

I receive every day a file with a specific pattern and extension, which I want to run under certain process. I want to check if there is any file in my folder, because otherwise I will do another task. So far I found that you can use a Script Task and do a File.Exist. However, I'm doing something wrong because it doesn't take the * as a wildcard. Devoluciones_source is "C:\Users\us1\Folder\" FileToSearch is "return"

My files: return_20200102.csv return_20200203.csv

String Filepath = Dts.Variables["$Project::Devoluciones_source"].Value.ToString() + Dts.Variables["User::FileToSearch"].Value.ToString() + "*csv";

        if (
            File.Exists(Filepath))

        {
            Dts.Variables["User::IsFound"].Value = 1;
        }

AimusSage · Accepted Answer · 2020-03-14T12:40:13.823

I don't think File.Exits() accepts wildcards, it checks the literal filepath and will return false because C:\Users\us1\Folder\*.csv is not found.

What you could do instead is get the files in the folder C:\Users\us1\Folder\ and checking those agains a searchPattern using Directory.GetFiles(path, searchPattern)

Like this:

string dirPath = Dts.Variables["$Project::Devoluciones_source"].Value.ToString(); 
string fileName = Dts.Variables["User::FileToSearch"].Value.ToString();

// if you want to make sure the directory exists:
if(Directory.Exists(dirPath) {
    string[] files = Directory.GetFiles(dirPath, fileName + "*.csv");

    if(files.lenght > 0) {
        // you can now iterate over each file that is found in the users directory that matches your pattern and do your logic.
    }
}

Some more info on the Directory.GetFiles method: Directory.GetFiles on docs.Microsoft.com

Some more info on the Files.Exists method: Directory.GetFiles on docs.Microsoft.com

How can I detect if there is any file in a folder using SSIS?

1 Answers1