SolvedPrivate

SSIS Package Error When Trying to Load Excel file into SQL

Posted on 2014-11-17
6
30 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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