Rowset error importing Excel 2007 spreadsheet into SQL 2005

Would it help to save it down as a comma delimited file with no formatting and then try importing or do you have to have it as an import of an Excel file?

Unfortunately the user requirement is that it needs to be imported as Excel file. As a fallback position I've established that we can save it as a csv file and import it that way, but users are insistent they need to import Excel 2007 file (as Excel 2007 overcomes Excel's previous limits of around 65,000 rows and increases possible no. of rows to over 1 million).

I bet you don't have "Sheet1$" table in your target SQL database and you will need to map each xlsx sheet into a different table (eventualy you can create them before the import with same structure as your xlsx sheet) then use those intermediate tables to agregate it into permanent SQL tables.

Have you seen this workaround?

http://www.sqlservercentral.com/Forums/Topic849224-148-1.aspx#bm1078370

At this post - Kathleen found an answer to a similiar question:

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

I needed to change the DelayValidation property of the Import task to True as well as the one in the Connection Manager

I think there was a similiar error at this page and it also stated to change the DelayValidation property of the Import task to True.

https://www.experts-exchange.com/questions/24257618/Opening-a-rowset-for-Sheet1-failed.html

Thanks for the replies ...

lcohan - I shouldn't need a Sheet1$ table in the target database : as I'm using the Import and Export Wizard in SSMS, the 'Complete the Wizard' step specifically states that 'The new target table will be created'.

stephenlecomptejr - although those links look useful, they all seem to refer specifically to SSIS packages, whereas I'm attempting the import MANUALLY using the Import and Export Wizard in SSMS.

Some further background points which might help explain my scenario ..
The SQL Server box has the 2007 Office System Driver Data Coonectivity Components installed, so that box has the ACEODBC.DLL fiole which  provides the Microsoft Office 12.0 Access Database Engine OLE DB Provider.
That OLEDB Driver shows up in SSMS under Server Objects-->Linked Servers-->Providers as
Microsoft.ACE.OLEDB.12.0 .
On the SQL Server box, sp_configure has been used to enable Adhoc Distributed Queries.

As the error reported was  an 'Opening a rowset ' error I thought I would try an OPENROWSET query :

    SELECT *  
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\MyExcel2007File.xlsx',
    'SELECT * FROM [Sheet1$]');
But that fails with an error of

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

It's almost as though SQL Server doen't think this OLE DB provider is available,

One final point - the SQL Server box being used is actually an SQL Service Cluster, so I don't know if that has any significance ...    

I believe the statement should be like below considering the space you have in the 'Excel 12.0' sring and please replace \\server\folder with the actual strings:

SELECT *  
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '"Excel 12.0;HDR=YES";Database=\\server\folder\MyExcel2007File.xlsx',
    'SELECT * FROM [Sheet1$]');

No lucl, lcohan, just gave the same result of :

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Try add IMEX=1 as well and change the following settings besides ad hoc queries but if possible do not do it directly in a production server:

1.EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
2.GO
3.EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
4.GO

IMEX=1 makes no difference - still getting same error (AllIInProcess and DynamicParameters were already set anyway) ....

Wow...is like OPENROWSET is looking for the linked server with that provider name...
Did you tried it with OPENDATASOURCE instead? Something like below:

--OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled.

SELECT * FROM OPENDATASOURCE
(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=\\server\folder\file.xlsx;Extended Properties=''Excel 12.0;HDR=YES''')...[Sheet1$]

No joy with OPENDATASOURCE either - that just throws back the 'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"' error .....

I'm just wondering whether the fact that the SQL Server box is an SQL Server Cluster has something to do with it. I'm not familiar with SQL Servwer Clustering, but I'm thinking that perhaps the action of installing 2007 Office System Driver Data Coonectivity Components (which makes the ACEODBC.DLL file and  Microsoft Office 12.0 Access Database Engine OLE DB Provider available) needs to be carried out on all machines in the cluster, so I'm going to investigate this with our DBA Team (unfortunately remote, and difficult to contact).

In the meantime, I've gone through this whole scenario on a standalone SQL Server 2005 box, and that works fine on that box - i.e. SQL Server 2005 can happily import the Excel 2007 spreadsheet, and the OPENROWSET queries also work OK.  

 

Have now contacted our DBA Team, and have asked them to check the clustering aspects of this.

I'll post a comment once they've done that, but in the meantime I'll split the points between stephenlecomptejr and lcohan as the comments from you both have been very helpful - so thanks to you both for your help ....