4

I am trying to import an Excel file (.xls) via SSIS to a table in SQL Server. But SSIS doesn't seem to recognize the file as a valid Excel file. I get the following errors:

Error 1:

[Excel Source [86]] Error: SSIS
Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Carga Base Original" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error 2:

[SSIS.Pipeline]
Error: Excel Source failed validation and returned error code 0xC020801C.

Error 3:

[Connection manager "Carga Base Original"] Error: 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: "External table is not in the expected format".

My connection manager properties is an Excel Source, its properties look like this:

Connection Manager Properties

I am passing the Excel file path through a variable. The Excel file looks OK and is not corrupted. I tried to put a fix Excel path, try to put it in Connection String property (fix path and variable), but nothing of this helped.

Can anyone please help me?

Improve this question
0

2 Answers 2

Reset to default
4

The main error is:

External table is not in the expected format

This happens when the Excel file is corrupted and cannot be opened by Access Database Engine (OLE DB provider) even if you can open the file from Excel. In a similar case opening the file manually and saving it as a new file may do the trick.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I did what you said and saved the file again with a new name, it worked.
Not always the solution but definitely worth checking first. This error is a generic catch all one that gets chucked out by OLEDB when it simply doesn't understand the source. It assumes it's a database - hence "Table" and after that it's sort of shrugs and goes "dunno". It can be very misleading.
@Ciarán You're right. I changed "will do" to "may do".
0

In my case saving the mail-merged reference Excel file as .xls (Excel 97 - 2003) worked. .xlsx provoked the "External table is not in expected format" error message but .xls did not.

Improve this answer

Comments

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.