0

I am trying to create a project that will produce a separate excel file for every account in a specific table. The first steps to pull the data into a temp file and load an array variable with the accounts for the loop, this is working fine.

I am getting the 2 errors below. And when I execute the package it fails on the export to excel data flow task.

Error 1 Error loading RAW_DATA_EXPORT.dtsx: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error 2 Error loading RAW_DATA_EXPORT.dtsx: The result of the expression ""\\server\DATA\Status\Testing\Filename" + @[User::Accts] + ".xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

This is what my dynamic connection string looks like evaluated: 

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\DATA\Status\Testing\Filename03500.xls;Extended Properties="EXCEL 8.0;HDR=YES";

I think the issue is since the excel file are non-existent, the validation fails when it gets to the step. From what I found online I need to either create the sheets through a a script task (not fond of) or use a variable value to create the sheet in the file. I have not been successful on either.

Holmes IV
  • 1,673
  • 2
  • 23
  • 47
  • Try to change the property `Delay validation` to `TRUE` for the Excel connection and the dataflow task – Kobi Feb 08 '16 at 16:36

1 Answers1

0

(...) since the excel file are non-existent, the validation fails when it gets to the step.

Have you tried delaying validation in the Data Flow task that exports to Excel? Check Data Flow's properties window.

RodWall
  • 149
  • 1
  • 9
  • The solution was to delay validation of the data flow, but also have a SQL execute task that would create the "table" in the excel file aka create the sheet. – Holmes IV Feb 08 '16 at 17:08