If you have ever tried to run or create a SSIS extract package that exports to Excel, you may run into the error below:
[SSIS.Pipeline] Error: Excel Destination failed validation and returned error code 0xC020801C.
Looking at the Progress tab, we get the error message (see below).
[Connection manager “Excel Connection Manager 1”] Error: 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”.
It looks very scary, but the solution is easy. The error is caused by SSIS running on a 64bit OS and install, and the Excel version I have is 32bit and they cannot establish a connection with each other.
The solution is as follows: Click Project at the top of the screen and at the bottom, click the project properties.
Under Configuration Properties, click Debugging. On the right side of the screen there is an option called: Run64BitRuntime which defaults to True….we need to change it to False like below. Click apply, and then Ok.
We then run our package again, and it now runs successfully, Yay.
SSIS is a very powerful tool and sometimes the error messages can be long, confusing or scary, but if you analyze the error message, you can usually fix your issue. Below is the link for Books Online for information concerning SSIS:
Thanks for reading and fix your error!