Exporting Data to Excel file From Sql Server 2014 using SSIS 2012

3
votes

I am using windows 8.1, office 2013, Visual Studio 2013, SQL Server 2014 and SSIS 12.0

I have a data flow which has OLE DB source, Data Conversion And Excel Destination. My OLEDB Source
enter image description here Data Conversion enter image description here I have create a Excel file StudentInfo.xls in (C:\XX) folder and make a connection enter image description here

Everything is OK, but when I execute it I giving me the following errors:

Error: 0xC0209303 at DynamicExcel, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error: 0xC020801C at Data Flow Task, Excel Destination [24]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

This link (Switch package from 64bit to 32bit) led me to change the Run64BitRuntime property to False.

After changing the property, I am now getting a different set of errors:

Error: 0xC0202009 at DynamicExcel, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".

Error: 0xC020801C at Data Flow Task, Excel Destination [24]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

2
sql-serverexcelvisual-studio-2012ssisdata-warehouse
it's probably an issue with permissions on the folder you're trying to write to (if it's not the obvious issue; the file being opened exclusively and/or write permissions on the file itself). – ZLK

2 Answers

3
votes

I've the same message, I have an SSIS project on visual studio 2010, I read a file.xls on that page, in my project VS 2010 has not any problem when I tried to store some query results to excel file, throw me a 'Microsoft.Jet.OLEDB.4.0' the provider is not registered on the local machine', I fixed that problem next following this steps,

1.-Open IIS

2.-Change the appPool on Advanced Settings

3.-true to enable to 32-bit application.

and that's all

ps.I changed Configuration Manager to X86 on Active Solution Platform

0
votes

I had the same issue. How I resolved it was:

  1. Open SSIS
  2. Go to DEBUG
  3. Click and open My Project Properties
  4. Go under Configuration Properties/Debugging
  5. Select False under Run64BitRuntime.
  6. Apply the changes

After I did this I was able to run the packages without any errors.