Extract Data from SharePoint excel using SSIS

ByHariharan Rajendran

Extract Data from SharePoint excel using SSIS

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.

  1. Visit – https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint/download
  2. Download 64 bit of Toolkit
  3. Install it on your system where you have installed the SQL Server Data Tools
  4. Create a new SQL Server Integration Projects
  5. Drag and Drop the data flow task into the control flow
  6. You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
  7. Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
  8. Configure SharePoint Connection Manager for SharePoint on-premises or online.
  9. Test the connection once.
  10. 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.
  11. Configure the Download Destination.
  12. Once excel files are downloaded then you can use that excel files to process further using excel source tasks.

Screenshots.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!