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

Improve this question
4
  • Instead of setting the ConnectionString property, what if you just specify the file in ExcelFilePath stackoverflow.com/questions/21536719/… Commented Jun 26, 2019 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 Commented Jun 26, 2019 at 16:57
  • Based on the expressions listed above. I think you are setting the Connectionstring expression within the ExcelFilePath property Commented Jun 26, 2019 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. Commented Jun 27, 2019 at 16:37
Related questions
1
SSIS connection expression problem
8
SSIS with variable excel connection manager
2
SSIS 2012 Dynamic OLE DB ConnectionString using a parameter
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.