I am doing a SSIS project right now. The goal for me is to read a specific file from the folder based on the File name. I have a Script Task written to read that speciic file, and I have the FullFilePath variable created to store that path after Script Task is run. The variable creation and the Script Task looks like below:

Variable Definition

`using System;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_35ccf8aff0e947b495fb51ace407dd67
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            try
            {
                // Get the values of the FolderPath and FilePattern variables
                string folderPath = Dts.Variables["User::FolderPath"].Value.ToString();
                string filePattern = Dts.Variables["User::FilePattern"].Value.ToString();

                // Show folder path and file pattern in message boxes
                MessageBox.Show($"Folder Path: {folderPath}", "Debug Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                MessageBox.Show($"File Pattern: {filePattern}", "Debug Info", MessageBoxButtons.OK, MessageBoxIcon.Information);

                // Get all files in the folder that match the pattern
                string[] files = Directory.GetFiles(folderPath, $"{filePattern}*");

                // Show the initial files found in message boxes
                foreach (var file in files)
                {
                    MessageBox.Show($"Found file: {file}", "Debug Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                // Filter files to include only those that match "FIMASTSALES" exactly, not "FIMASTSALES2"
                files = files.Where(f => Path.GetFileName(f).StartsWith(filePattern) && !Path.GetFileName(f).StartsWith($"{filePattern}2")).ToArray();

                // Show the filtered files in message boxes
                foreach (var file in files)
                {
                    MessageBox.Show($"Filtered file: {file}", "Debug Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                if (files.Length == 1)
                {
                    // Set the FullFilePath variable to the found file
                    Dts.Variables["User::FullFilePath"].Value = files[0];
                    MessageBox.Show($"Full file path set to: {files[0]}", "Debug Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    // Show error message if no file or multiple files are found
                    MessageBox.Show($"Expected exactly one file with pattern '{filePattern}', found {files.Length}.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
            catch (Exception ex)
            {
                // Show the exception message in a message box
                MessageBox.Show($"Exception: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}`

it runs perfectly and the messagebox also shows the FullFilePath is set correctly.

Variable Placement within Script Task.

Now comes the problem, when I use the Flat File Source Connection Manager to put the varaibles into the expression, it says the Variable is empty…

Error Message

I am confused now, because even testing FilePatter and FolderPath variable, it will return the same error but they have an orginial value that is never getten overwritten.

read a specific file from the folder based on its name using SSIS