Excel Connection String in SSIS

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.

excel
ssis
connection-string
asked on Stack Overflow Feb 27, 2019 by Sultan • edited Feb 27, 2019 by EzLo

2 Answers

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.

answered on Stack Overflow Feb 27, 2019 by Sultan
0

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

enter image description here

answered on Stack Overflow Feb 27, 2019 by Srikar mogaliraju

User contributions licensed under CC BY-SA 3.0