I took this C# code from the internet which basically gets the latest file path I have in a directory.
public void Main()
string files = System.IO.Directory.GetFiles(@"C:\SSIS\Files");
DateTime lastDate = new DateTime();
string lastFile = string.Empty;
foreach (string f in files)
finf = new System.IO.FileInfo(f);
if (finf.CreationTime > lastDate)
lastDate = finf.CreationTime;
lastFile = f;
Dts.Variables["User::LastFile"].Value = lastFile;
Dts.TaskResult = (int)ScriptResults.Success;
I put the code in a Script Task and I was successfully able to get the path of the latest Excel file which is written to the [User::LastFile] variable.
However, I want to pass this variable to an Excel connection manager. I've done it through Expressions (in properties of the Excel connection manager)>ExcelFilePath property and chosen the variable as @[User::LastFile] variable.
When I setup an Excel Source Task and try to select the name of the Excel Sheet it says: Invalid argument.
Can anyone suggest how I can get my variable Excel full path successfully into an Excel connection manager to work in a Data Flow task so that I can eventually import that Excel file?