onesegun
asked on
SSIS Pass Excel path variable from a script task to a data flow task
Hi All,
I took this C# code from the internet which basically gets the latest file path I have in a directory.
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?
Thanks
OS
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");
System.IO.FileInfo finf;
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?
Thanks
OS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's the only solution that works
Open in new window
Not sure on the C# script..