SSIS error when configuring .xls data flow task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

SSIS error when configuring .xls data flow task

Author

Topic

jstikal
Starting Member

19 Posts

Posted - 2013-07-07 : 22:52:02

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!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:36:30

Are you using 32 bit drivers for connection? If yes, Have you set 64 bit runtime to false in BIDS?
whats the connection string you use for connecting to xls?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jstikal
Starting Member

19 Posts

Posted - 2013-07-08 : 07:52:17

I think I am using the 32 bit version, the JET directory is located in the WOW3264 folder in the registry.

I have the setting set to false located in Project -> Properties -> Config Properties -> Debugging
Are there any other locations that I need to configure?

Here's the connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\...\file.xls;Extended Properties="Excel 8.0;HDR=YES";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 11:28:52

theres a corresponding property in sql agent as well called 32 bit runtime. you need to check it.

Also check this if using jet driver i 64 bit server

http://www.connectionstrings.com/Articles/Show/using-jet-in-64-bit-environments

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jstikal
Starting Member

19 Posts

Posted - 2013-07-08 : 16:02:46

Thank you visakh16. I will be sure to verify the agent setting once the package is deployed.

I looked at the driver and I already have the following installed:

[url]http://www.microsoft.com/en-us/download/details.aspx?id=23734[/url]

This driver resolved my package that's importing a .xlsx file but the .xls is still trying to connect to the JET driver with the same "External table is not in the expected format" error.

Also additional information in the technical detail:

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:17:48

This is the ACE driver isnt it? In that case are you using connection string as the below for connecting to xls sheet? Its different from JET provider


Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<yourfilepath>.xls;
Extended Properties="Excel 8.0;HDR=YES";

source:
http://www.connectionstrings.com/excel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jstikal
Starting Member

19 Posts

Posted - 2013-07-10 : 10:34:29

I want to first thank you visakh16 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 10:58:46

quote:
Originally posted by jstikal

I want to first thank you visakh16 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.

oh...that was a good twist
anyways glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources