July 6, 2013 at 9:50 pm
Quick environment details: 64 bit Server 2008; SQL Server 2012; Studio 2010
I have a package that connects to a .xlsx file via the MS access driver and works but I have a second package that requires a connection to a old .xls file and I'm receiving the following error when trying to configure the data flow task:
"Exception from HRESULT: 0xC020801C
Error at Package[Connection manager Ëxcel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: Ëxternal table is not in the expected format."
Due to the incompatibility with the 64 bit OS I'm running the project in 32 bit mode. I had issues with the access driver as I do not have MS office installed on the server but manually installing the driver resolved package #1 with the .xlsx file.
Any assistance with package #2 with the .xls file and Jet drive would greatly appreciated.
Thank you!!
July 8, 2013 at 12:13 am
Can you convert the .xls to a .csv? It would make your life easier.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 8, 2013 at 6:08 am
Agreed but unfortunately I do not have any flexibility with the file type. I would convert to .xlsx if I had the option as I already have a package successfully running using the MS Access DB driver.
July 8, 2013 at 6:20 am
JoshuaT (7/6/2013)
Quick environment details: 64 bit Server 2008; SQL Server 2012; Studio 2010I have a package that connects to a .xlsx file via the MS access driver and works but I have a second package that requires a connection to a old .xls file and I'm receiving the following error when trying to configure the data flow task:
"Exception from HRESULT: 0xC020801C
Error at Package[Connection manager Ëxcel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: Ëxternal table is not in the expected format."
Due to the incompatibility with the 64 bit OS I'm running the project in 32 bit mode. I had issues with the access driver as I do not have MS office installed on the server but manually installing the driver resolved package #1 with the .xlsx file.
Any assistance with package #2 with the .xls file and Jet drive would greatly appreciated.
Thank you!!
Is the .xls file invalid? When exactly do you get this error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 8, 2013 at 7:24 am
Not sure about the drivers but see if below workaround works for you -
Add a script task in the package to save/convert .xls file as .xlsx with proper formats...configure delay validations and execute this task only if .xls file is being processed..
:rolleyes:
July 8, 2013 at 8:38 am
The file is valid. I can open in Excel on a local machine without an issue.
I receive the error in the "Excel Source Editor" when clicking on "Columns". Also, the "Name of the Excel sheet" drop down results in "No tables or views could be loaded".
July 8, 2013 at 8:54 am
Thank you HakunaMatata for the recommendation. Make sense but I apologies for my ignorance, can you provide or guide me to some resources with the code the that will save the file in the new .xlsx format?
July 8, 2013 at 9:31 am
JoshuaT (7/8/2013)
The file is valid. I can open in Excel on a local machine without an issue.I receive the error in the "Excel Source Editor" when clicking on "Columns". Also, the "Name of the Excel sheet" drop down results in "No tables or views could be loaded".
Try these two things and post what happens:
1. Delete the Excel file connector, then delete and recreate the Excel source file in the directory it is being read from with a differant name, then create the excel file connector again pointed to the new file.
2. Open the Excel source in the data flow task and select query as the source instead of Sheet. Use the Query editor to see if you can add the sheet and select all columns from it.
This will take care of any issues that might occur if your source file became locked, cached in memory, sheet renamed or ouyt of order etc...
It might also change your error just enough to get a better idea of what is going on.
July 8, 2013 at 12:38 pm
Thank you PHYData DBA.
I followed the listed steps and when I select "Build Query" from the Excel Source editor the following are the details of the error:
===================================
The Query Builder cannot be used to edit this query. (Microsoft Visual Studio)
===================================
Failed to connect to the source using the connection manager 'Excel Connection Manager' (Microsoft.DataTransformationServices.Design)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.PipelineUtils.GetActiveConnection(ConnectionManager connectionManager, IServiceProvider serviceProvider, Control control, IsConnectionCurrentCallback IsConnectionCurrent)
at Microsoft.DataTransformationServices.Design.PipelineUtils.GetCompleteConnectionString(ConnectionManager connectionManager, IServiceProvider serviceProvider)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.buildSqlQueryButton_Click(Object sender, EventArgs e)
July 8, 2013 at 2:48 pm
I attempted to write a simple select statement directly in query editor and received the follow error:
TITLE: Microsoft Visual Studio
------------------------------
Exception from HRESULT: 0xC020801C
Error at Package [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: "External table is not in the expected format.".
Error at Data Flow Task [Excel Source [69]]: 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.
------------------------------
BUTTONS:
OK
------------------------------
July 8, 2013 at 11:59 pm
JoshuaT,
You can refer below link on how to convert spreadsheet to new format.. This script will go in script task.
http://blog.quasarinc.com/ssis/save-as-a-new-excel-file-in-ssis-2012-script-task-using-visual-basic/
You can use variable to read the file name and check if filename has extension as .xls then execute above script task else skip this script task..
:rolleyes:
July 9, 2013 at 6:24 am
I would recomend a reinstall or install of the SSDT for VS 2010 at this point. Also is this the server or your development system that this is occuring on?
Either way try to update or install MDAC on each one.
Once everything is confirmed patched and running at the latest releases and it still does not work look into this.
KB933835 http://support.microsoft.com/kb/933835
The SQL Server Agent Service account, account used by SSIS runtime, and your logon each use a different Temp directory when you are logged into and executing SSIS directly on the server. The actual directory used is located under C:\Documents and Settings\AccountName\Local Settings\Temp. Once the SSIS package execution Proxy account is granted read and write access to this directory the package should start acting better.
If you still have the issue there is a bug specific to SQL 2008 that is listed here. If you have not installed this fix try it.
http://support.microsoft.com/kb/933835
Technet.microsoft.com and msdn.microsoft.com are full of good information on this error if you search. Be sure to try limiting your search to just items in the Library and knowledge base before trying the items posted to the social links.
Good Luck 😎
July 10, 2013 at 8:34 am
I want to first thank everyone for your time and assistance. I really appreciate it!
I recreated my package and .xls file and in doing so I found that my file even though had a .xls extension and opened in excel without any issues was actually formatted as a .csv. This was the cause for the table format error. Once I saved the file in excel as .xls, the error was resolved. Unfortunately the application that provides the file does not truly deliver a .xls but merely alters the extension from .csv to .xls.
July 10, 2013 at 8:39 am
JoshuaT (7/10/2013)
I want to first thank everyone for your time and assistance. I really appreciate it!I recreated my package and .xls file and in doing so I found that my file even though had a .xls extension and opened in excel without any issues was actually formatted as a .csv. This was the cause for the table format error. Once I saved the file in excel as .xls, the error was resolved. Unfortunately the application that provides the file does not truly deliver a .xls but merely alters the extension from .csv to .xls.
I have had that issue before.
Why I recomended deleting and recreating your source file.
I had forgot about this behavior but the new versions of EXCEL open files with extension of xls as a speardsheet if it can interpet the data into a set of field and row.
December 24, 2015 at 6:45 am
SSC Rookie,
Maybe your Excel file was saved in one format, but then the extension was changed. Did you open it and Save As the desired format, with the corresponding extension?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply