I'm trying to import data which from Excel which has been "prettied up" to make it readable to users. The report has a fixed number of columns and the data itself is well structured, but there are blank lines etc.
The problem I'm having is unless the first row of the spreadsheet contains or headers, SSIS fails with error "External table is not in the expected format." and the exception below is thrown.
So I thought I'd be clever and create a template spreadsheet with the same number of columns and set the first row to strings - I made all columns DT_WSTR thinking I could extract the numeric and datetime data from the actual rows which contain data and convert using a script. But SSIS simply ignores the numeric values from the spreadsheet - all I get is String or Date values and nulls for all numeric cells?
Dave
Error at Monthly Balance [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.".
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Dave,
You may find the DataDefractor SSIS Data Flow Source useful. It's main purpose is to allow you to extract factual and contextual information from human-readable reports and then flow this data into the SSIS pipeline. You can download a free beta at http://www.datadefractor.com.
Cheers,
Vassil
