0

I've spent all morning looking at this and I'm going in circles.

I'm setting up a SSIS Package to import all the Excel Timesheets in a folder, I started off with only one file however so to keep things simple added just one data flow task, I'll come back to add the loop later.

I created a connection to the Excel file and added the Excel Source using this connection. Also worth noting is that the headings may change so deselected the First Row has column names box.

I then added an OLE DB Destination and the columns all match up. I also then added a SQL task prior to all this to truncate the destination table.

This all works fine, however I then created a string variable called ExcelFile with a value of C:\Data\Timesheets\TS1.xlsx.

The connection string of the Excel Connection Manager was 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Timesheets\TS1.xlsx;Extended Properties="Excel 12.0 XML;HDR=NO";

and all I want to do is add the ExcelFile Variable to replace the source, so I added a connection string expression with the following value:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties='Excel 12.0 XML;HDR=NO';"

This evaluated as expected, but when I save this the package then fails with the following error:

Validation error. ISSTimesheetImport Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".

This was supposed to be the simple bit, but its driving me crazy. Any help would be greatly appreciated.

EzLo
  • 13,780
  • 10
  • 33
  • 38
Sultan
  • 53
  • 1
  • 9
  • Set the Delay Validation on the Excel connection to true. SSIS is validating that the connection can be established with your excel, but since the expression isn't built correctly until runtime, it fails. Right click the Excel connection and open properties, you can edit it there. – EzLo Feb 27 '19 at 12:11
  • I tried that on both the Connection and on the Data Flow Task and got the following error: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. – Sultan Feb 27 '19 at 12:15
  • I just found the problem, I'd at some point changed the double quotes to single quotes to get the expression to validate, however I should have changed them to \" so it's now working fine. – Sultan Feb 27 '19 at 12:21
  • Further examples of setting the ExcelFilePath property https://stackoverflow.com/questions/13961534/how-do-i-pass-value-to-a-stored-procedure-parameter-in-ole-db-source-component/13976990#13976990 & https://stackoverflow.com/questions/21536719/dynamically-assign-filename-to-excel-connection-string/21536893#21536893 – billinkc Feb 27 '19 at 14:27

2 Answers2

1

I just found the problem, I'd at some point changed the double quotes to single quotes to get the expression to validate, however I should have changed them to \" so it's now working fine.

Sultan
  • 53
  • 1
  • 9
0

Instead of updating the connection string, use that variable for ExcelFilePath under expressions.

enter image description here

Srikar mogaliraju
  • 215
  • 1
  • 10