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.

 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;
        }

Open in new window

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

Jim Horn

The VB.NET script task would go like this..

Public Sub Main()
Dts.Connections("YourExcelFileConnection").ConnectionString = Dts.Variables("LastFile").Value
End Sub

Open in new window

Not sure on the C# script..

ASKER CERTIFIED SOLUTION

onesegun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

onesegun

ASKER

It's the only solution that works