SolvedPrivate

SSIS Package Error When Trying to Load Excel file into SQL

Posted on 2014-11-17
6
29 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now