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

Thanks
Sutha