Building Dynamic Excel Connection String
If you wish to build the Excel connection string dynamically inside SSIS and assign it to Excel Connection Manager you can use the following expression.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::strWorkingFolder] +"\\"+ @[User::strExcelFileName] + ";Extended Properties=\"" + "Excel 8.0;HDR=YES" + "\""+";"
Where strWorkingFolder and strExcelFileName are user variables. For example, strWorkingFolder = C:\Temp and strExcelFileName = Sales.Xls
Please note the above syntax is for Excel 97-2003.
WARNING - Out of the box Excel Drivers are NOT available in 64 Bit environment.
Thanks
Sutha