Hello everyone I have a little issue which I'm unable to resolve. Here is the context:
I have a 64-bits SQL Server 2014 instance on my computer and a 32-bits Office installation (on which the only component installed is Excel, no Word, no Office Tools, no Office Shared Features...). I try to import some data stored in a .xls file on some network drive on a daily basis, but I'm unable to find the right way to do it. Here's why:
I try this command:
INSERT INTO BBImport
SELECT * FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=W:\..\aaa.xls;Extended Properties=''''Excel 8.0;HDR=Yes''''')...[Sheet5]
After resolving all the permission issues, I finally got this error:
"The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server."
Note: I also tried with Microsoft.ACE.OLEDB.12.0, same error.
I try to install Microsoft ACE 64-bits, but it is not compatible with my 32-bits Office Installation -_-...
However I succeed to import the data by using the import wizard of SQL by specifying the followings:
Data Source: Microsoft Excel | Excel Version: Microsoft Excel 97-2003 (or 2007 both worked) | Destination: SQL Server Native Client 11.0
Since that worked, I created a SSIS package for that and import it into my Integration Services Catalog into a new project. But when I try to right-click/Execute, my SQL Server Management Studio stops working and I get those errors in my event viewer:
1. Application: Ssms.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.IO.FileNotFoundException
Stack:
at Microsoft.SqlServer.IntegrationServices.UITasks.ExecuteTask.ShowReport()
at System.Threading.ThreadHelper.ThreadStart_Context(System.Object)
at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean)
at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
at System.Threading.ThreadHelper.ThreadStart()
2. Faulting application name: Ssms.exe, version: 2014.120.2000.8, time stamp: 0x5306c06d
Faulting module name: KERNELBASE.dll, version: 6.1.7601.18409, time stamp: 0x53159a86
Exception code: 0xe0434352
Fault offset: 0x0000c42d
Faulting process id: 0x8d8
Faulting application start time: 0x01d06c81bcb45ec2
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe
Faulting module path: C:\Windows\syswow64\KERNELBASE.dll
Report Id: 84f7630f-d87a-11e4-a40a-005056ae5914
3. Fault bucket , type 0
Event Name: CLR20r3
Response: Not available
Cab Id: 0
Problem signature:
P1: ssms.exe
P2: 2014.120.2000.8
P3: 5306c06d
P4: mscorlib
P5: 4.0.30319.18063
P6: 526766b5
P7: 170b
P8: 14
P9: System.IO.FileNotFoundException
P10:
Attached files:
These files may be available here:
C:\Users\administrator.CASGRAIN\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_ssms.exe_5c1853936685e7ed85eece7cc3339e468fa8b7_0aee4fe2
Analysis symbol:
Rechecking for solution: 0
Report Id: 84f7630f-d87a-11e4-a40a-005056ae5914
I Google those errors, but did not find any fix.
And when I try to execute it via a stored procedure by running this code, but nothing happens (no results, no message and my table is still empty).
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'BBImport2.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'test', @project_name=N'BBImport2', @use32bitruntime=False, @reference_id=NULL
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
Do anybody have an idea to help me?
I finally solved my issue by simply uninstalling Office 32b and installing Office 64b. After a lot of test, the problem was either the compatibility between SQL(64) and Office(32) or the file wasn't fully import since the excel had a lot of vlookup function that search into other sheets and those sheets needed to run the bdp function (Bloomberg function) to be filled.
I successfully run this command to import my xls file:
INSERT INTO main SELECT * FROM
OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0','Data Source=[path\filename.xls];Extended Properties=EXCEL 8.0')...[tablename]
I still can't understand why it was running from the wizard and from VS2013 when I run the package manually, but not when I was importing the package into the SSIS catalog or when I was executing the query directly. It's working now. I guess that it's the only thing that matters...