Having trouble importing Excel 2007 to SQL Server 2005

Question

I am using the SSIS Import/Export wizard to import data from an Excel 2007 file to SQL Server 2005.  The table in the server does not exist yet, so it will be created when the wizard runs.  I am receiving the following errors when clicking Finish on the wizard:

• Could not connect source component.
• Error 0xc0202009: Source - Users$ [1]: An OLE DB error has occurred. Error code: 0x80004005.
• Error 0xc02020e8: Source - Users$ [1]: Opening a rowset for "`Users$`" failed. Check that the object exists in the database. (SQL Server Import and Export Wizard)

I selected the Microsoft Office 12.0 Access Database Engine OLE DB Provider and typed "Excel 12.0" in the Extended Properties field in the "All" tab. I also tried "Excel 12.0 Xml;HDR=YES" and still no success.  Everything works fine until I click Finish.  I also selected the "Copy data from one or more views or tables" option.

All help is much appreciated! 

Thanks in advance, 

Terence

Answer 1

hello tarcenea

actually what u have tried seems to be true... in the extended properties field, "Excel 12.0" is true...

in Sql Server Import and Export Wizard's Choose a Data Source window u clicked on properties button... i guess u might have typed a wrong path t ur excel file... i mean if its true, then there must be no error... have u clicked on "test connection" button to see if it succeed to connect ur file?

Answer 2

Terence, is Users a sheet in the workbook? Also, is this a 32-bit or 64-bit version of SQL Server?

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 3

Tunchinho:

Yes I clicked "Test Connection" every time and it would succeed. And if I typed the wrong file path, the wizard would stop me before proceeding any farther unless of course there was an excel file with that path.

John Welch: 

Users is the first sheet in the workbook.  I temporarily solved the problem by doing a File>Save As> Excel 97-2003 instead of Excel 2007.  The version I have is 32-bit.

Answer 4

And the "Preview Data" succeeds and shows you the contents of the Users worksheet, after you select "Users$" as the table to import from?

-Doug

Answer 5

Terence,

We hope that you've resolved the problem.

For most of what I know about common issues with the Excel driver, please see my blog:
http://dougbert.com/blogs/dougbert/archive/tags/Excel/default.aspx

-Doug