SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR - The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available

In the SSIS package which using OLE DB provider Microsoft.ACE.OLEDB.12.0 to get the values from the Excel sheets is failing only when I run through the SQL Agent Job, but while run through the Business Intelligent Studio or SQL Server Integration Service it is not failing, when I close look at the error message it clearly says that the “The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available”. In this case, I found the reason, run the package through the Business Intelligent Studio or SQL Server Integration Service, by default 32-bit driver is using, but run via SQL Agent Job in 64-bit environment, it’s calling 64-bit driver.

Solution: There is an option in SQL Agent, under Execution Option, where we can turn on use 32 bit run time.

Detail Error Message:

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered -- perhaps no 64-bit provider is available.  Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"   Hresult: 0x80040154  Description: "Class not registered".  Code: 0xC020801C Source: Extract Data OLE DB Source [711] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  

Cheers!