Looking for a simple way to Insert Data into your SharePoint Farm from SQL, Provide Reporting Services through SharePoint and use SharePoint not only as a Dashboard for the retrieval of Information but as the data entry point to allow data synch to/from numerous entities… A good place to start is with the Microsoft SQL Server Integration Services on CodePlex and the SharePoint List Source and Destination Adapter…

The adapter is written in C# with Linq .Net 3.5 and provides the following:

Extensive Validation:

Validation for this component actively goes against the SharePoint Site to verify the properties are valid.

Values from Expressions:

This component supports external variables, which can be expressions, and can be attached to the source component to customize the query. Similar to the CommandText for the other Sql Components

Linq:

The Component has been written using Linq with .net 3.5 and shows how elements such as the metadata and columns can be combined to create a readable usage in a Linq format.

Custom properties:

The component keeps its configuration in custom properties on itself, inputs, and input columns.

There is also an MSDN Tutorial By Kevin Idzi, Software Development Engineer, Microsoft Corporation Extracting and Loading SharePoint Data in SQL Server Integration Services

There a few different ways to extract or load SharePoint data:

1. · Use the SharePoint APIs to add or remove items one by one.

   · Use the SharePoint Batch API to generate XML and submit the XML.

   · Use the Lists Web service, which uses the same XML as the Batch API.

2. 
   The SharePoint List source and destination use public SharePoint Web services and have several features that enhance their performance and their ease of use:
3.  

· Only the fields that you want are returned from SharePoint.

· Large lists are not transferred all at once.

· Column type information from SharePoint is used for mapping to Integration Services data types

· CAML queries can be added to the query to filter the rows to be returned.

· Update and Delete operations from an Integration Services package are simple.

· Important custom properties of the source and destination can be set sing SSIS expressions.

All I can say is thank you for providing an easy to use adapter that plugs into Visual Studio, simplifies the Creation and Maintenance of SQL ETLs, and enhances the value of SharePoint….

-Ivan