The original error message will be like this,
[Excel Source from Direct [16]] Error: SSIS Error Code DTS_E_
Solution will be to Open the Visual Studio/BIDS–>Right Click at Project level –> Open Properties–>SetRun64BitRunTime as False
In Production mode the Error will be like,
The requested OLEDB provider Microsoft.ACE.OLEDB.12.0 is not registered
Solution is to Right Click the Job from SQL Server Agent–>Open Properties–>Go To Steps –> Click Edit–>Open Execution Options Tab –> Use 32 bit run time
Commentary: I was told the above two error occurs when we have our development machine running a 64 bit OS and our SQL Server is 32 bit so we have to force it to run at 32 bit.I believed the same, but accidentally I found my Server was 64 bit when I was working on an other issue, So what’ causing this? This error happens only when a package uses any of Microsoft Office Product like Excel or Access database, to be more precise the Microsoft Products are installed as 32 bit by default in any machine, so the associated drivers with Microsoft products like ACE.OLEDB.12.0 doesn’t supports our 64 bit environment,hence we force them to run at 32 bit.
