Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 38
  • Last Modified:

SSIS Package Error When Trying to Load Excel file into SQL

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
StampIT
Asked:
StampIT
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
sarabhaiCommented:
Can you check the project property page under configuration properties set the Run64BitRuntime to False
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Valliappan ANCommented:
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
 
Valliappan ANCommented:
This should give more details on above:

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

Cheers.
0
 
StampITAuthor Commented:
Thanks for the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now