Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS error when configuring data flow task with .xls file Expand / Collapse
Author
Message
Posted Saturday, July 6, 2013 9:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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!!
Post #1470962
Posted Monday, July 8, 2013 12:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:06 PM
Points: 5,047, Visits: 11,797
Can you convert the .xls to a .csv? It would make your life easier.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1471047
Posted Monday, July 8, 2013 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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.
Post #1471135
Posted Monday, July 8, 2013 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 PM
Points: 13,616, Visits: 10,505
JoshuaT (7/6/2013)
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!!


Is the .xls file invalid? When exactly do you get this error?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1471137
Posted Monday, July 8, 2013 7:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:55 PM
Points: 918, Visits: 491
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..


Post #1471181
Posted Monday, July 8, 2013 8:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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".
Post #1471223
Posted Monday, July 8, 2013 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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?
Post #1471236
Posted Monday, July 8, 2013 9:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 335, Visits: 298
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.
Post #1471251
Posted Monday, July 8, 2013 12:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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)

Post #1471321
Posted Monday, July 8, 2013 2:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 6:30 AM
Points: 18, Visits: 97
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
------------------------------
Post #1471372
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse