You’re probably getting this error message in BIDS/Data Tools
Error 1 Validation error. Data Flow Task: Package1: The requested OLE DB provider Microsoft.Jet.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. Package1.dtsx 0 0
The issue here is that your computer is 64 bit and both versions (32 bit and 64 bit) of Excel are installed. By installing Microsoft Access Database Engine 2010 Redistributable your issue should go away but this alone did not work for me.
What worked for me is to also install 2007 Office System Driver: Data Connectivity Components which corrected the Microsoft.Jet.OLEDB.12.0 issue.
File Type (extension) Extended Properties
Excel 97-2003 Workbook (.xls) “Excel 8.0”
Excel 2007 Workbook (.xlsx) “Excel 12.0 Xml”
Excel 2007 Macro-enabled workbook (.xlsm) “Excel 12.0 Macro”
Excel 2007 Non-XML binary workbook (.xlsb) “Excel 12.0”
I hope this helps solve your issue.