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

11 Replies

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 NameConfiguration on the Script TaskDescription
SearchPathReadOnly, InputThis is the path to be searched
SearchPatternReadOnly, InputPattern to be searched
FileExistsReadWrite, OutputA 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.

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.

11 thoughts on “#0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory”

      1. nakulvachhrajani Post author

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

        Like

  2. Apau

    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

    Like

    Reply ↓
    1. nakulvachhrajani Post author

      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.

      Like

      Reply ↓
    1. nakulvachhrajani Post author

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

      Like

      Reply ↓
      1. Brian

        The directive System.Linq needs adding to the top of the script. Would be good if you could add this to your instructions

        Like

  4. Sven Texas

    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.

    Like

    Reply ↓

Let me know what you think about this post by leaving your feedback here!

This site uses Akismet to reduce spam. Learn how your comment data is processed.