I am working with SSIS - 2005 I have successfully implemented a package that creates csv files with the name dynamically generated. This is done in a datatask wrapped in a foreachloop. The datatask has a Flat File Destination (with a flat file connection manager) that sets the 'connectionstring' property dynamically(using expressions), based on a Manager_id variable passed from the foreachloop. So far, so good. When I attempt to do the same with an Excel destination(with an excel connection manager), I can't figure out which property to set dynamically - there is 'connectionstring', 'ExcelFilePath' and 'ServerName'. I use the variables in the build of the exprerssion for each property, but am getting OLE_Db errors.

What are your errors?Craig Bryden - Please mark correct answers

Not done it on export but on import, setting the connectionstring dynamically for excel files doesn;t seem to work well so I use the ExcelFilePath and ExcelVersion which works fine for importing - I would guess it would be the same for exporting as wellRgds Geoff

I've used both ConnectionString and ExcelFilePath and they both work fine.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

ExcelFilePath works fine. However, you would need to create the structure of the excel sheet prior to executing the data flow task since the excel sheet is being created dynamically. You may use a SQL task and fire a create table on the excel connection. HTH.

I use an expression on 'ExcelFilePath' as well.... works fine.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi, Please use ExcelFilePath that should be fine. Thanks, NaveenNaveen Kumar

Thanks All, I ended up following the step by step guide presented here ......... http://www.rafael-salas.com/2006/12/import-header-line-tables-into-dynamic_22.html There are a few traps but you must remember to use the same excel connection manager for all steps, as well as setting 'delay validation' to true. The process involves setting up a variable e.g. ExcelFileNames, and setting its properties to update it from the foreachloop via an expression. Then apply this variable in an expression setting for the property ExcelFilePath in the excel connection manager used for the destination. Works well....I need a cup of tea and a good lie down after that one.