Hi @KR_24 ,
As you said that the package runs well in SSDT but fail in SQL Server, please help to run the package in 32bit run-time for a try.
You may also check below link to troubleshoot the issue.
how-do-i-troubleshoot-ssis-packages-failed-execution-in-a-sql-agent-job
I'd suggest that you may load the data to local folder first and then use SSIS Script Task to upload the file to Sharepoint.
I'm not familiar with SP, check sharepoint-online-url to see if you could find the list URL.
ssis-upload-file-to-sharepoint-document-library-revisited
Uploading documents to WSS (Windows Sharepoint Services) using SSIS
Regards,
Zoe
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.