SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Monday, July 23, 2007

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

1 Comments:

  • At 12:54 PM, Blogger SUMAN said…

    Thank you very much..it saved me a lot of time..

     

Post a Comment

Links to this post:

Create a Link

<< Home