How to declare a C# variable that returns a sliced string of another variable in my SSIS package?

An overview of the process is below, with a couple C# Script Tasks used in this example. For the Script Tasks be sure to add the proper references as indicated below, with these being the using statements at the beginning of each section of code.

• Add an Execute SQL Task to the beginning of the Control Flow. The file names from the current execution will be held in a table that will be created in this task. Example SQL for this is below. This checks if the table exists, drops the table if it's present, and then creates it.
• You probably already have the file name for each iteration stored in a variable, but if not add a string variable at Index 0 of the Foreach Loop. This can be done on the Variable Mappings pane.
• Within the Foreach Loop add a Script Task after all the components. Add the string variable holding the file name in the ReadOnlyVariables field. This script task (uses C#) will insert the file name used in each iteration to a table that will be used later. While doing the insert with C# is a slightly more involved than just an Execute SQL Task, the FileInfo.Name property can be used to ensure only the file name is stored, as opposed to the file name with path.
• After the Foreach Loop create another Script Task. If variables are used to hold the name of the output folder and file prefix add these in the ReadOnlyVariables field as before. Code for this is also below. First the names of the files from the current execution are retrieved and stored in the currentExecutionFiles list. The files in the output directory are then enumerated through, with any files that have the prefix and were not used in the current package execution deleted.
• This is optional, but I'd recommend adding another Execute SQL Task at the end of the package to drop the table holding the file names. The DROP statement from the first Execute SQL Task can just be used for this.

CREATE and DROP DDL for Execute SQL Task:

IF (OBJECT_ID(N'DATABASE.SCHEMA.FILES') IS NOT NULL)
BEGIN
DROP TABLE DATABASE.SCHEMA.FILES 
END;

CREATE TABLE DATABASE.SCHEMA.FILES
(
[FILENAME] VARCHAR(250)
)

C# Script Task to Store File Names:

using System.Data;
using System.Data.SqlClient;
using System.IO;


string currentFile = Dts.Variables["User::FileNameVariable"].Value.ToString();
FileInfo currentFileInfo = new FileInfo(currentFile);
//Integrated Security for Windows authentication
string connectionStr = @"Data Source=Server;Initial Catalog=Database;Integrated Security=SSPI;";
string cmd = @"INSERT INTO DATABASE.SCHEMA.FILES ([FILENAME]) VALUES (@file)";

 using (SqlConnection conn = new SqlConnection(connectionStr))
 {
     using (SqlCommand sql = new SqlCommand(cmd, conn))
     {
         SqlParameter pFileName = new SqlParameter("@file", SqlDbType.VarChar);
         pFileName.Direction = ParameterDirection.Input;
         pFileName.Size = 250;
         //use FileInfo.Name to get only file path
         pFileName.Value = currentFileInfo.Name;
         sql.Parameters.Add(pFileName);
         conn.Open();
         //insert file name
         sql.ExecuteNonQuery();
     }
 }

C# Script Task to Delete Old Files:

using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;


string outputFolder = Dts.Variables["User::OutputFolder"].Value.ToString();
string filePrefix = Dts.Variables["User::FilePrefix"].Value.ToString();

DirectoryInfo di = new DirectoryInfo(outputFolder);
List<string> currentExecutionFiles = new List<string>();

string connectionStr = @"Data Source=Server;Initial Catalog=Database;Integrated Security=SSPI;";
string cmd = @"SELECT [FILENAME] FROM DATABASE.SCHEMA.FILES";

using (SqlConnection conn = new SqlConnection(connectionStr))
{
    using (SqlCommand sql = new SqlCommand(cmd, conn))
    {
        conn.Open();
        using (SqlDataReader dr = sql.ExecuteReader())
        {
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    //get file names from table holding names from current execution
                    currentExecutionFiles.Add(dr[0].ToString());
                }               
            }
        }
    }
}

foreach (FileInfo fi in di.EnumerateFiles())
{
    //check for file with prefix (case insensitive) that was not in current execution
    if (fi.Name.StartsWith(filePrefix, StringComparison.InvariantCultureIgnoreCase)
                        && !currentExecutionFiles.Contains(fi.Name))
    {
        fi.Delete();
    }
}