In this article, we will discuss how to load data into SQL Server from SharePoint using SQL Server Integration Services (SSIS). This process can be a bit challenging due to security issues, but we will provide step-by-step instructions to help you overcome them.

Getting Started

Let’s start by understanding the scenario. Imagine you have a client who has multiple Excel spreadsheets on a SharePoint instance. One of these spreadsheets is updated daily, and the client wants the data from that particular spreadsheet to be loaded into SQL Server by a nightly process.

The most efficient and effective way to achieve this is by using SSIS. We will be using SQL Server Integration Services 2008 for demonstration purposes, but the steps we will be discussing are applicable in SQL Server 2014 as well.

Creating an Integration Services Project

To begin, create a new Integration Services Project. This will provide you with a working surface where you can define the source of your data and the target for your raw data.

Defining the Source Data

As our data source is an Excel spreadsheet on SharePoint, we need to create and configure an ‘Excel data source’ in SSIS. Drag and drop the Excel data source from the toolbox onto the working surface.

Configure the data source by clicking on the ‘Browse’ button and providing the UNC format SharePoint link to the spreadsheet. You will be prompted to enter your security credentials for SharePoint.

Once the data source is configured, you can select the specific sheet containing the data to be imported. You can also define the source columns that will be displayed in the Data Flow design sheet.

Setting the Data Destination

Next, we need to define the data destination, which is a staging table in our SQL Server database. Add an OLEDB destination to the work surface and configure it by setting the field mappings between the source spreadsheet and the destination table fields.

Loading Multiple Spreadsheets

If you have multiple spreadsheets to load, the process is similar to what we have just covered. You can repeat the steps for each spreadsheet, configuring the data source and destination accordingly.

Testing the Project

To test the project, you can choose the debug tab and click ‘Start Debugging’. This will execute the package and load the data into the SQL Server table. You can examine the loaded table to ensure that the data has been successfully imported.

Dealing with Security Issues

Working with SharePoint often involves security challenges. When pulling data from a SharePoint site via a scheduled job, it is important to prevent SharePoint from challenging the job each time it is run.

One workaround is to disconnect the process ID and leave it running in the background. This will keep the security token intact and allow the package to run without issues. However, scheduling and executing the package from the SQL Server Agent may still encounter problems.

In such cases, you can use the Windows Task Scheduler on the server to schedule the job. This requires creating an execution string to call the package and execute it. The DTEXECUI tool in SQL Server can be used to generate the command line string for this purpose.

Scheduling the Nightly Job

To schedule the job in the Task Scheduler, simply start typing ‘Task’ in the search screen and open the Task Scheduler. Create a new task and specify the run time for the job in the ‘Trigger’ tab.

In the ‘Action’ tab, select the SQLShack.bat file (which contains the command line string) as the program to run. Save the task and you will see it listed in the Task Scheduler. You can right-click on the job to run it and confirm that everything is working correctly.

Conclusion

Moving data from SharePoint to SQL Server can be challenging, especially when it comes to security issues. However, by using SSIS and following the steps outlined in this article, you can successfully load data from SharePoint into SQL Server.

Remember to test your project thoroughly and consider potential workarounds for any security-related challenges you may encounter. With the right approach, you can automate the process and ensure that your data is seamlessly transferred from SharePoint to SQL Server.

Happy programming!