1

I have an sql server connection( source ) and an Excel destination on my d:/ drive. I would like to rvide a dynamic file name to the excel file using a expression: In the connection manager:

ExcelFilePath "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::File_Path] + @[User::FileName_Product_Hierarchy_XLS] + ";Extended Properties=\"" + "Excel 8.0;HDR=Yes;IMEX=1;"+ "\""

The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.

The file name is generated using a SQL task and assigned to the variable @[User:: FileName_Product_Hierarchy_XLS]

I also tried to add a quote at the end but did not work.

The SSIS Package is not transferred to an SQL Server, there is no SSIS catalog, it is purely n design mode on a normal windows machine.

Any Ideas what I need to change to make it work?

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::File_Path] + @[User::FileName_Product_Hierarchy_XLS] + ";Extended Properties=\"" + "Excel 8.0;HDR=Yes;IMEX=1;"+ "\"";"

I already tried to set quotes like:

in the connection manager:

ExcelFilePath "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::File_Path] + @[User::FileName_Product_Hierarchy_XLS] + ";Extended Properties=\"" + "Excel 8.0;HDR=Yes;IMEX=1;"+ "\""

the result should be that the Excel File receives the File name provided by the SQL statement, which basically only changes the date.

I changed the setting "DelayValidation to True" Still getting the error

Excel Connection Manager settings

Expressions editor

Evaluation of expression

Matthias
  • 21
  • 3
  • Instead of setting the ConnectionString property, what if you just specify the file in `ExcelFilePath` https://stackoverflow.com/questions/21536719/dynamically-assign-filename-to-excel-connection-string/21536893#21536893 – billinkc Jun 26 '19 at 14:11
  • in SSIS create a variable and use an expression in the variable to build your string: Then use that variable in your connection string that will just be the string you created in your variable – Brad Jun 26 '19 at 16:57
  • Based on the expressions listed above. I think you are setting the `Connectionstring` expression within the `ExcelFilePath` property – Hadi Jun 26 '19 at 18:49
  • Thank you all vey much for your input. I tried all attempts but Excel and SSIS just do not like each other. I found a differnt solution/workaround using the SSIS file tasks. Generating the variable using a sql task, handing over the value to the variable. Creating a template with only the headers. The dataflow task loads the data into the template. Using a renaming file task, the excel file is renamed using the expression containing the dynamic file name. – Matthias Jun 27 '19 at 16:37

0 Answers0