请求修改SSIS包中的文件存在性检查代码以支持通配符

Hey there! Great to help you expand that file check functionality to handle wildcards. Let's adjust your script to look for any files matching the *DoR pattern in your User::FilePath directory. Here's the modified code with clear explanations:

Modified Script #

public void Main()
{
    // Get the target directory path from your SSIS variable
    string folderPath = Dts.Variables["User::FilePath"].Value.ToString();
    // Define our wildcard search pattern - matches any file ending with "DoR"
    string searchPattern = "*DoR";
    bool fileExists = false;

    try
    {
        // First verify the directory exists to avoid unnecessary errors
        if (System.IO.Directory.Exists(folderPath))
        {
            // Use EnumerateFiles for efficient wildcard matching (lazy-loads results)
            var matchingFiles = System.IO.Directory.EnumerateFiles(folderPath, searchPattern);
            
            // Set flag to true if we find at least one matching file
            fileExists = matchingFiles.Any();

            // Log all matching files for debugging purposes
            bool fireAgain = false;
            foreach (var file in matchingFiles)
            {
                Dts.Events.FireInformation(0, "SCR File Check", $"Found matching file: {file}", string.Empty, 0, ref fireAgain);
            }
        }
        else
        {
            // Log a warning if the target directory doesn't exist
            bool fireAgain = false;
            Dts.Events.FireWarning(0, "SCR File Check", $"Target directory does not exist: {folderPath}", string.Empty, 0);
        }
    }
    catch (Exception ex)
    {
        // Catch and log unexpected errors to avoid task crashes
        bool fireAgain = false;
        Dts.Events.FireError(0, "SCR File Check", $"Error checking for files: {ex.Message}", string.Empty, 0);
        // Uncomment below if you want the task to fail on errors
        // Dts.TaskResult = (int)ScriptResults.Failure;
        // return;
    }

    // Update the SSIS variable with our final result
    Dts.Variables["User::FileExists"].Value = fileExists;

    // Keep your original variable logging for visibility
    bool fireAgain = false;
    string message = "{0}::{1} : {2}";
    foreach (var item in Dts.Variables)
    {
        Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
    }

    // Log our search parameters for debugging clarity
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "local", "Search Directory", folderPath), string.Empty, 0, ref fireAgain);
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, "local", "Search Pattern", searchPattern), string.Empty, 0, ref fireAgain);

    Dts.TaskResult = (int)ScriptResults.Success;
}

Key Changes Explained #

• Wildcard Matching: Replaced the single file check with Directory.EnumerateFiles using the *DoR pattern, which finds any file whose name ends with "DoR" (works for files like InvoiceDoR.csv or SummaryDoR).
• Directory Validation: Added a check to confirm the target directory exists first, preventing unhandled exceptions.
• Enhanced Logging: Added logs for matching files, directory warnings, and search parameters to make debugging easier.
• Error Handling: Wrapped the file check logic in a try/catch block to catch and log unexpected issues without breaking the task (you can enable task failure on errors if needed).

If you need to adjust the pattern (e.g., match files containing "DoR" anywhere with *DoR*, or specific extensions like *DoR.xlsx), just modify the searchPattern variable!

内容的提问来源于stack exchange，提问作者sqldba