.xlsx connection error: External table is not in the expected format

Question

.xlsx connection error: External table is not in the expected format

amy.walsh

Ten Centuries

Points: 1168
More actions
February 10, 2012 at 11:04 am

#251358

When I create my connection manager to connect to a .xlsx file, I get the error message that says:
"Test connection failed because of an error in initializing provider. External table is not in the expected format."
The provider selected was the ole db "Microsoft office 12.0 Access Database Engine...", and in the All tab I entered "Excel 12.0" at the top where it says Extended properties.
Here is the stumper:
My import package(s) has (have) been running manually for the last 2-3 weeks, but now i've finished an automated system that downloads the files from the ftp site, unzips them, validates all the expected files are there, renames and relocates the files to the names/locations expected by my ETL, and then runs the ETL.
I found out today that the people who manually did this process were each time opening the file and then saving it to the correct location in Excel 2007.
When I try to run my ETL on the files in the automated process, it fails with the above error. If, however, I just open the file and save it (also in 2007), it runs fine. I have been trying to figure out what saving the file is doing to change it to be in the expected format.
I have tried to use an excel 2003 connection to connect to a copy of the file with a modified extension of .xls and that didn't work. (in case it was in another version of excel and someone just changed the ext)
I then tried to access the file with the OLE DB provider, as above, but instead of entering 12.0 on the Extended properties line, entering 9.0 or 14.0 in turn (creating new sources/connections each time), and when I do that, both times I get an error that says:
“Test connection failed because of an error in initializing provider. Could not find installable ISAM.”
Am I missing something? Is there something else that simply opening and saving the file might be doing to make it paletable to ssis?
If we install excel on the db server I realize i could probably open it up and save it as excel 2007 and that would do the trick, but the dba is resistant to this idea, so I thought I'd see if anyone has any other ideas?
If we do have to install excel on that server, does the version have to be excel 2007 in order to programmatically save it as type 2007?
thanks in advance for your thoughts on the issue...
Amy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

This may not be the issue, but is your package running in 64-bit mode? IF it is try try executing it with the 32 bit DTEXEC. Here's a link for 2005 since that's the forum you are in. If you are running 2008 it's easier and here's a link for that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

amy.walsh Ten Centuries Points: 1168 More actions · Answer 2

Thank you for your reply. I actually am running it in 32 bit mode -- I had that issue a short while ago when we moved to a 64-bit OS, and I had to set the debug runmode in the solution properties.

I am pretty sure I figured out the issue though. I found a thread on another site where people were having the same issue.

I figured out that the .xlsx file was actually saved in 2010, which is a 64-bit application. Unfortunately, there is no way to tell if a file was saved in 2007 or 2010, so that's what threw me off. there are no notifications when you open the spreadsheet created/saved in 2010 that you are actually converting it to 2007!

But there is definitely a difference behind the scenes somewhere, because SSIS doesn't like to connect to it the same way it connects to 2007 version spreadsheet.

I had tried to enter 14.0 in the properties of the connection, but got an ISAM error. I traced this error back to the fact that the Microsoft.ACE.OLEDB.12.0 provider can only be used for excel 2007 (32-bit) data sources. You need to install the 64-bit ACE driver in order to read 2010 excel data sources.

The problem, however, is that you can't install both the 32 and 64 bit versions side-by-side, and the 64-bit cannot be used to connect to the 32-bit 2007 source.

So we have no choice but to install excel 2007 so i can write a script task to convert the 2010 file to 2007. Otherwise, it seems we would have to set up a whole new environment for all those clients wanting to send files in excel 2010 format.

Hopefully this post will help someone else as frustrated as I was last friday...

take care,

Amy

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

I've imported Excel 2010 files before without a problem.

I'll try at home tonight to make sure.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

amy.walsh Ten Centuries Points: 1168 More actions · Answer 4

yes, please let me know. Are you using the ACE 12.0 provider? or the ACE 14.0?

If it's ACE 14.0, can you still load 2007 files? what about 2003?

If it's ACE 12.0, and it did work on a 2010 excel file (please make sure that it is definitely 2010 and no 2007) what does your connection string look like? what's in your extended properties?

Also, are you using SSIS 2008 or 2005? We have not upgraded to 2008 yet, so without installing the 64-bit driver, the ACE 14.0 provider is not an option in the drop-down list of providers. Is it in yours?

thanks in advance for trying to help me sort this all out -- if you can do it, then there may actually be a solution out there so we don't have to convert every 2010 file before running! It'll then just be a matter of figuring out what's different in your environment than ours...

thanks again,

Amy

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Allright, I did the test.

* Created an Excel file in 2010. In the Save As window, there is no way to differentiate between 2007 and 2010, so my guess is still that they are identical

* Created a project in BIDS 2008R2 (which is the same as BIDS 2008). I don't have BIDS 2005 available, so I couldn't test that one.

* Created Excel connection manager and read from the file without any problem. Provider is ACE 12.0, Excel is version 12.0. Connectionstring:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test2010.xlsx;Extended Properties="Excel 12.0;HDR=YES";

So nothing special here.

I think the problem is BIDS 2005. It is 2005, so it can't read 2007 or 2010 🙂

If you want to read Excel files with the .xlsx extension, you'll need to upgrade to 2008, or downgrade the Excel sheets themselves to 2003 (*.xls).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

If you can use third-party solutions, check the commercial CozyRoc Excel adapters. They are part of the CozyRoc SSIS+ library. All Excel versions from 97-2010 are supported.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/