April 18, 2011 at 5:43 pm
I am trying to use the Excel Connection Manager in SSIS 2008 R2. I have set Run64BitRuntime = false. I have tried to process several .xls files that have one worksheet. But I get an "unexpected error from external database driver (22)" message from them. When I use the original worksheet tab name (for example, POS Report-XXXX BRANDS-YYYYYYY), I get the error. But if I rename the worksheet tab something simple like "TEST" I don't get the error. Is there a way around this other than manually renaming the tab for each .xls file?
April 19, 2011 at 12:10 am
The space in the name is possibly the issue.
Try writing a SQL query to your Excel file instead of just selecting the sheet name in the dropdown list.
There you can put the sheet name in brackets, what will solve the issue:
[POS Report-XXXX BRANDS-YYYYYYY$]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 10:51 am
I tried
SELECT * FROM [SHEET NAME$]
I received the following error. Is my syntax incorrect?
TITLE: Microsoft Visual Studio
------------------------------
Error at XXXX_YYY_WEEKLY [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unexpected error from external database driver (22).".
Error at Data Flow Task [Excel Source [29]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
April 19, 2011 at 11:12 am
There is a query builder that you can use. That way you don't have to worry about syntax.
I would advise you however not to use SELECT *, but to specify the column names.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 11:25 am
When I try the query builder I get the following error.
TITLE: Microsoft JET Database Engine
------------------------------
Unexpected error from external database driver (22).
------------------------------
BUTTONS:
&Retry
Cancel
------------------------------
April 19, 2011 at 11:27 am
You don't have the Excel file open, so it is locked?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 11:36 am
I corrected the SQL syntax so now it works.
SELECT [POS Report-XXXX BRANDS-YYYYYYYY$].*
FROM [POS Report-XXXX BRANDS-YYYYYYYY$]
April 19, 2011 at 11:41 am
imani_technology (4/19/2011)
I corrected the SQL syntax so now it works.SELECT [POS Report-XXXX BRANDS-YYYYYYYY$].*
FROM [POS Report-XXXX BRANDS-YYYYYYYY$]
Ah, apparently the table needs to be aliased. Strange...
But my advise still stands: drop the * and use the real columns.
If someone types into a non-used column and then deletes the data, SSIS will still think there is a new column and it will add the column to the dataflow, resulting in a package failure.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 11:58 am
I spoke too soon. When I try to run the package, I get the following error:
Error at Data Flow Task [Excel Source [29]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task [SSIS.Pipeline]: component "Excel Source" (29) failed validation and returned error code 0xC020801C.
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at XXXXXX_POS_WEEKLY [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unexpected error from external database driver (22).".
April 19, 2011 at 12:03 pm
The package already fails at validation. Does the package has permission to access the folder where the Excel file is stored?
The Excel file isn't locked by another process?
This may sound silly, but have you tried turning it off and on again? 😀
Restart BIDS, it may help.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 12:14 pm
I restarted BIDS and checked the permissions. I still get an error.
April 19, 2011 at 1:08 pm
I had this error way back in the day. If you are using a new version of Excel - 2010 and you SQL Cerver Version is 2008 the Jet engine is is Excel is incompatible with SSIS 2008. You will not encounter this Jet engione error idf you use A version of Excel 2007 and earlier. Hope this helps.
April 19, 2011 at 1:11 pm
I had this error way back in the day. If you are using a new version of Excel - 2010 and you SQL Server Version is 2008 the Jet engine in Excel 2010 is incompatible with SSIS 2008. You will not encounter this Jet Engine error if you use Excel 2007 and earlier. Hope this helps.
April 19, 2011 at 2:23 pm
I'm using SQL Server 2008 R2 and Excel 2007. I hope this helps.
April 19, 2011 at 2:42 pm
imani_technology (4/19/2011)
I'm using SQL Server 2008 R2 and Excel 2007. I hope this helps.
So, if I'm not mistaken, you are using a .xlsx file, right?
If yes, you should use the ACE provider, not the JET provider. (In your original question you said you were using .xls files.)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply