SSIS- Download and Load Excel from Sharepoint to S

Question

I currently have a situation where there is a excel file on a sharepoint site. I need to load that excel file to a database. The Excel file has mulitple sheets. I have tried the follow methods with zero luck.

Method 1- I went to the sharepoint site and went to the library tab and selected the open with explorer link. I have used that link as the file location in the excel connection manager. I was successfully able to run the ssis package locally but it fails when it runs on the server (insufficent permission). I later found out that we are unable to directly connect to sharepoint to do ETL.

Method 2- I tried using http connection manager with zero luck.

I think the best way to do this is using the script task in SSIS. However i am fairly new to script tasks so if anyone can provide a code and a way to use it that would be great. I did look at some sample code online but I was unable to find one that worked correctly (it might very well be me that was using it incorrectly)The file will need to overwrite any files with the same name.

For this example lets use these below

Sharepoint Path= http://example.com/example/excel.xlsx
Local Path= C:\

Answer 1

Have you tried the methods described in this article - link. It gives a detailed step by step breakdown of how to handle SharePoint List data as source or destination.

Answer 2

You can download excel to a local drive from sharepoint ( https://stackoverflow.com/a/45553754/8430404) then map each excel tab in ssis to load into database

or theres also a c# approach to load all excel tab data. (http://www.techbrothersit.com/2016/04/c-import-excel-sheet-data-with-excel.html?m=1)

Be sure to have primary key/index in your destination table or you may end up with dupes. Also the headers of excel and destination tables should match (exact)