Excel Connection Manager and expressions

Hello,

I have a question regarding which expressions need to be set to have the excel connection manager be able to create/update a file dynamically. I'm reading a path in from a database and storing it in a variable. The path is just the location of an excel file. What I would like to have happen is that the excel connection manager be dynamic so that if I wanted to change where this file went or the name I could simply change the value in the database.

I've tried setting the expressions, in the Excel connection Manager: ConnectionString,ExcelFilePath, and Servername to the variable \\path\folder\file.xls with no luck. The ConnectionString seems to be a little funny because it contains Provider=Microsoft.Jet.OLEDB.4.0;Data source = ; Extended Properties =; HDR=YES", so I don't know if I can just use that expression and ignore the others or what.

Any help would appreciated,

Phil

Phil,

I think you are on the right track. In order to make the path and file name dynamic, you just need to use an expression against ExcelFilePath property. The only caveat thought is that even when the ExcelFilePath is dynamic; that it won't create the file 'on the fly'. The work around is to use an Execute SQL task to create it before it gets used in the dataflow. I have an example of that in my blog:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

I hope you find it helpful

|||

Yea, you were right. It's just a matter of setting the expression in the control flow instead of the data flow. I really appreciate it.

Phil

sql