Using SSIS and Connecting to Sharepoint

Creating a package that connects and gets data from sharepoint. There are currently 3 methods that can be used in using sharepoint data with Microsoft SQL Server SSIS.

Use the opensource source and destination adapter.

This method is quick and easy to use. However, it is somewhat limited in the parsing of information.

This method is also from opensource and is not currently supported by Microsoft so some organizations will not install this on their production SQL servers.

Create a WSDL and webservice to call the data.

This method is fairly easy to use. The problem with this method is the WSDL must be stored on the local drive of the SQL server where the SSIS package resides. This is not overly acceptable. (or you must be able to download from the server where the WSDL resides. You would have to put this in your Disaster recovery plan to ensure that you do not loose your WSDL file or the methods it calls. You can use the out of box method calls or design your own methods in your WSDL file.

Lastly you can create a class assembly and reference it in your SSIS package.

This is a great method if you need to reuse and keep a consistent way of accessing Sharepoint. You can continue to add and recompile your source code to add extra features and it can be reused for other packages.

You can also create your parsing conditions and they can be stored and resused for the other packages.

The downside is the initial time it takes to create your class assembly.

Recommended method is the adapter for its easy of use and quick start. Our IT department is moving toward the third method to create a cohesive solution for the entire shop.