SolvedPrivate

SSIS Package Error When Trying to Load Excel file into SQL

Posted on 2014-11-17
6
27 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
truncate SQL 2008 database log file 3 35
SQL 2012 Syntax Error 5 24
Record extraction 3 14
Sql query 34 19
SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

16 Experts available now in Live!

Get 1:1 Help Now