#0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory

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).

Files existing in the directory to be searched

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

Table 1: Variables on the SSIS package

Screenshot showing the variables and their configuration on the script task

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:

Screenshot showing the script in debug mode indicating that files were found matching the pattern.

As you can see, the script can help perform a wild-card search in a given folder or directory.

irasri April 30, 2021 at 9:03 PM

sorry its ToList()

LikeLike

Reply ↓
1. nakulvachhrajani Post authorApril 30, 2021 at 9:10 PM
  
  That is interesting. Can you please share some more details (what version of Visual Studio BI are you using, whether you have added the appropriate “using” statements, etc)?
  
  LikeLike

Apau June 1, 2021 at 6:00 PM

Same problem here with ToList() and Any()
VS prof 2017 15.9.16
C# tools 2.10.0 beta
I have added following name spaces:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections;
#endregion

LikeLike

Reply ↓

nakulvachhrajani Post authorJune 21, 2021 at 8:17 PM

Thanks, @Apau – please see my notes below – the issue may be that your directory is empty and hence appropriate handling may be required before a ToList is done.
The demo is purely for demonstration purposes and hence I have not added any error handling in.

LikeLike

Reply ↓

Brian June 21, 2021 at 6:27 PM

Getting the error. How do we fix this?

LikeLike

Reply ↓

nakulvachhrajani Post authorJune 21, 2021 at 8:14 PM

A point to check is whether the folder exists and has any elements in it.
The System.IO.Directory.EnumerateFileSystemEntries() method basically returns an IEnumerable of strings – which we are then converting to a List via “ToList”. If the directory is empty – ToList will return a NULL reference exception.
Another point that comes to mind is whether the executing process has rights to the directory you are using.

The post is intended to demonstrate the concept – but you can debug the script task to get the exact exception and troubleshoot based on that (https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/debug-a-script-by-setting-breakpoints-in-a-script-task-and-script-component?view=sql-server-ver15).

LikeLike

Reply ↓
1. Brian June 22, 2021 at 3:39 PM
  
  The directive System.Linq needs adding to the top of the script. Would be good if you could add this to your instructions
  
  LikeLike
2. nakulvachhrajani Post authorJuly 17, 2021 at 7:51 PM
  
  Thank-you, Brian! I have updated the post with a note indicating that the using System.Linq; directive must be used.
  
  LikeLike

Sven Texas June 22, 2021 at 9:26 AM

Thanks, following this through on my package – however, erroring at ToList() and Any(), IEnumerable’ does not contain a definition for ‘ToList’ and no accessible extension method ‘ToList’ accepting first argument of type ‘IEnumerable’ can be found, same for Any(). You have not mentioned any “using” statements to add in your article. Can you help please? The article is brilliant to use as a reference if it worked. Thanks.

LikeLike

Reply ↓