Jump to content

Extracting and Loading SharePoint Data in SQL Server Integration

Whenever it comes to the loading of SP Data in the server integration of SQL, the very first thing that developers of integration package services are bothered about is the best- possible ways in which the data can put in as well as brought out from the SharePoint list. The source of the list and the destination sample of the SharePoint offers an optimized solution with a user friendly interface. The sample also comes with an API for an efficient accomplishment of the tasks apart from the Integration Services.

Ways of Working with SP Data

Following are some of the prominent ways in which the SharePoint data can be extracted or loaded:

  • Making use of the SharePoint APIs to add or remove items individually
  • Generating as well as submitting the XML by means of the use of the SharePoint Batch API
  • Using the Lists Web service that makes use of the same XML as the Batch API

The web services come up as great ways of transferring information to and from SharePoint without altering the platform server itself.

SharePoint Source List and Destination: Features

A discussion on the prominent features of the list source as well as destination of SharePoint is essential in this regard. Now, these actually make use of the public web services of the platform. Here is a list of features that improve their level of performance and functionality:

  • The required fields are only returned from the web application development platform
  • The somewhat large lists are divided in batches with a batch size that can be configured
  • The information of column type is utilized for the purpose of mapping to Integration services data types
  • Addition of CAML queries to the query is facilitated to separate the rows that need to be returned
  • Simple updating as well as deleting of operations from the Integration Services package
  • Integration services expressions can be used to set the essential custom properties of the source and destination
  • Supports SQL Server as well as x64 and x86 processors

Process of Using the Destination and Source

Few steps need to be taken prior to using these parts of this SharePoint development platform. The foremost thing to be done is to download the right installer package from the codeplex site. Once downloaded, the installer needs to be run. It has to be kept in mind in this regard that Complete Install needs to be selected, as this allows the installer deploy connectors for each of the version of SSIS on the targeted machine. This is followed by the final step that involves addition of the source and destination to the Toolbox.

Extracting the Information from SharePoint with List Source

The extraction is usually done through the following steps:

  • Addition of the SP connections
  • Addition of components to the SSIS data flow
  • Setting the properties of the SharePoint list source'
  • Writing data to SP by means of the SP list destination

With regards to the extraction and loading of SP information, few more factors need to be considered apart from the above. For instance, if you are looking up for a value in a SP list, the Lookup transformation in the data flow can be used to load the lookup table. Again, if you are replacing values in the data with the values found in the loop, loading the altered data back to SP can be done by including the ID from the lookup column.

There is absolutely no doubt about the point that the SP list source as well as destination sample makes getting data into and out of a SharePoint list much hassle- free. What's more, the sample also facilitates a utility library that makes working with SP lists out of integration services simpler.

We provide SharePoint bespoke development services. If you would like to talk to one of our certified SharePoint programmers, please get in touch with us at Mindfire Solutions.