SolvedPrivate

SSIS Package Error When Trying to Load Excel file into SQL

Posted on 2014-11-17
6
33 Views
Last Modified: 2016-02-11
I am attempting to load the contents of an Excel(97-2003) spreadsheet into a SQL table using  an Excel Source - Data Conversion- and SQL Server Destination in a SSIS package. The package errors at the Excel Source. The error "The Excel Connection Manager is not supported in the 64 bit version of SSIS, as no OLE DB Provider is available". How do I correct this ? Thanks.
0
Comment
Question by:StampIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40448446
Not sure you can, as SSIS has had a lot of issues with the jump from 32-bit to 64-bit and data sources, especially Excel.
I recommend avoiding this entirely by saving your Excel as a .csv, and then in SSIS using a Flat File to import.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40448487
Also, just to 'check the box' here, Excel to SSIS is not a rock-solid path, as SSIS requires a 'contract' between it's source and destinations in order to pump data correctly, and Excel is, let's just say, very easy for users to edit in such a manner that could break that 'contract'.
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 250 total points
ID: 40449145
Can you check the project property page under configuration properties set the Run64BitRuntime to False
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 250 total points
ID: 40449197
This link explains the issue, as sarabhai also pointed out the setting.

http://help.pragmaticworks.com/dtsxchange/scr/FAQ%20-%20How%20to%20run%20SSIS%20Packages%20using%2032bit%20drivers%20on%2064bit%20machine.htm

From above link:
DTExec 32-Bit  can be found under  : C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

DTExec 64-Bit  can be found under  : C:\Program Files\Microsoft SQL Server\90\DTS\Binn
 
For more information click on the following URL

http://msdn.microsoft.com/en-us/library/ms162810.aspx

Open in new window


Basically, you need to use the 32 bit version of DTSExec to execute.

An alternative would be to use the 64bit driver and 64bit connection to use the same in SSIS;
http://blog.oraylis.de/2011/05/ssis-goodie-1-excel-ssis-64-bit-dts_e_oledb_excel_not_supported/

Look for Access 64 bit driver from Microsoft.com and download and then you could use the Access 12.0 connection string, to connect the same.
 
HTH.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40449208
This should give more details on above:

http://technet.microsoft.com/en-us/library/ms141766%28v=sql.105%29.aspx

Cheers.
0
 

Author Closing Comment

by:StampIT
ID: 40450040
Thanks for the help.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question