There is no direct way of extracting the SharePoint Excel data to SQL Server using SSIS. However, we can process multiple steps to achieve the result.
This post explains that how we can download the excel files from SharePoint and store it in a local drive and then process the stored excel from SSIS.
To download the files easily, we can use the tool called “SSIS Integration Toolkit for Microsoft SharePoint”.
Follow the below steps.
- Visit – https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint/download
- Download 64 bit of Toolkit
- Install it on your system where you have installed the SQL Server Data Tools
- Create a new SQL Server Integration Projects
- Drag and Drop the data flow task into the control flow
- You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
- Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
- Configure SharePoint Connection Manager for SharePoint on-premises or online.
- Test the connection once.
- Drag and drop the “SharePoint Source”. Point the created SharePoint connection and Choose the source list as “Documents” or the library where you have stored the excel files.
- Configure the Download Destination.
- Once excel files are downloaded then you can use that excel files to process further using excel source tasks.