Connect SharePoint to SQL Server through SSIS
- Open Visual Studio and create a new Integration Services Project.
- Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
- In the Data Flow screen, add a DataReader Source and an OLE DB Destination from the Toolbox.
Extract Data from SharePoint List using SSIS
- Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
- Use the Data Flow Task.
- You can see “OData Source” task. …
- This OData Source task required a connection manager. …
- Test the connection.
Follow the steps mentioned below by navigating to the SharePoint List:
- List Actions > Export to SpreadSheet/Excel.
- Save the Excel file.
- Open your SQL database with SQL Management Studio.
- Right click on your database > Tasks > Import Data.
- Select your Excel file as the source, and specific table as destination.
When a SharePoint list is curated to the point that it is trustworthy as a source of data, it can be integrated into ETL processes using SSIS. Using the OData source in the SSIS data flow, you can easily retrieve data from a SharePoint list into your data pipeline.
Export the external list to Excel
- Navigate to the SharePoint site that contains the list.
- On the Quick Launch, select the name of the SharePoint list, or select Settings. …
- Select List, and then select Export to Excel. …
- If you are prompted to confirm the operation, select OK.
- In the File Download dialog box, select Open.
PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE
- Create a Data Flow in your package.
- Go to the Data Flow tab and drag the SharePoint List Source into the data flow as your source.
- Double click on the source. …
- Paste the URL for the SharePoint site into the SiteURL (exclude anything from the word Lists onward in the URL)
The SharePoint Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from the Microsoft SharePoint server.
When you create a new List-Item in SharePoint, it is stored in the appropriate List-Item table in SQL down to an individual page. You can only store so many rows in an 8KB page, so depending on the type of list item, it may be stored across one or multiple pages.
Is SSIS part of SQL Server?
SSIS stands for SQL Server Integration Services. SSIS is part of the Microsoft SQL Server data software, used for many data migration tasks. It is basically an ETL tool that is part of Microsoft’s Business Intelligence Suite and is used mainly to achieve data integration.
With Microsoft SharePoint Designer 2010, you can connect to a variety of databases, including Microsoft SQL Server, Oracle, and other databases that are accessible by using the OLE DB protocol. By default, your Microsoft SharePoint site does not contain any database connections.
An external list is a key building block for SharePoint solutions based on external data. You can read and write external data with the familiar experience of using a SharePoint list. An external list looks and behaves a lot like a native list, but there are some differences in behavior.
As mentioned by every poster so far, it is not supported to access SharePoint lists directly through SQL. However we had a very obscure use case with an Access 2010 web database where we needed to query the lists directly through SQL.