I’ve created a simple package to fetch the data from SQL Server to pupulate the Excel file using Excel Destination SSIS Component, when I tried to Execute, it throwed below error
Error: 0xC020801C at Data Flow Task, Excel Source : 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.
This issue is occurred because my system has 64 bit OS and Office Excel object. most of the office products does not have 64bit provider and hence the error above on Excel connection. This Issue can be fixed by setting Run64BitRunTime property as False at SSIS Project properties.
Go to properties page for the overall SSIS solution, simply set the Run64BitRunTime = False.
Refer the screenshot below for the further reference