SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 SSIS error when configuring .xls data flow task
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jstikal
Starting Member

15 Posts

Posted - 07/07/2013 :  22:52:02  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/08/2013 :  01:36:30  Show Profile  Reply with Quote
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
Go to Top of Page

jstikal
Starting Member

15 Posts

Posted - 07/08/2013 :  07:52:17  Show Profile  Reply with Quote
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";

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/08/2013 :  11:28:52  Show Profile  Reply with Quote
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
Go to Top of Page

jstikal
Starting Member

15 Posts

Posted - 07/08/2013 :  16:02:46  Show Profile  Reply with Quote
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:

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

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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/09/2013 :  01:17:48  Show Profile  Reply with Quote
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
Go to Top of Page

jstikal
Starting Member

15 Posts

Posted - 07/10/2013 :  10:34:29  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/10/2013 :  10:58:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000