Microsoft has acknowledged this shortcoming and has released the following package to rectify it using "newer" Access/Excel Connection managers. However, after installing and following the instructions - the same issue occurs.
The cause of the problem: The problem is caused due to a limitation that exists with office versions 1997-2003. The connection manager was designed for Office 1997 and has not been amended or improved since. This is why, when you actually research limitations on Office 2000 or 2003, you'll see that they do not allow more than 255 columns (the limit mentioned above) AND the length of the column-name cannot surpass 64 characters. These are software limitations that existed with Office 2000 and 1997 and since the Connection Manager was designed then, they just remained with it given MS did not invest in improving or updating their product. Hope this helps!
In my other consideration it may due to OLEDB provider version older version issue
Follow below link it may help you a lot
[https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/][1]
Last Solution:
As you said your package is working fine but not in deployment mode right ?
So why didn't you try DTEXEC command line execution for this package. Schedule it using Windows Task Scheduler
Follow below link.
https://www.mssqltips.com/sqlservertutorial/218/command-line-tool-to-execute-ssis-packages/
