It's been a while, but since I now get to play with SQL 2016 full time, I have new things to talk about! One of major difficulties that any technology pro has to deal with is integrating different systems. Vendors really do try to make this as easy as they can - but the main pain points they usually focus on is intra-generational versus inter-generational. And sometimes when the vendor falls down (looking at you Microsoft), some enterprising people craft solutions - but even then, it's usually intra-, not inter-generational.
You Can't Get There From Here
Specifically, you can connect to SharePoint 2007 with SQL Integration Services 2008R2 using Kevin Idzi's SharePoint List Adapter components provided by Microsoft as "samples". I've used that many times, and even though it's rough around the edges, what wasn't about 2008R2? It got the job done - and at the end of the day, that's what we're looking for, right? (And I'm pre-excusing the roughness of what's coming... :))
Microsoft improved things with the OData support in SharePoint 2010+, and built-in OData source in SSIS 2016. But again... that's intra-generational. How does one connect to a long-in-the-tooth SharePoint instance that just hasn't yet been migrated off of with a brand-spanking-new BI stack? Tough nut to crack, as the SharePoint List Adapter is in beta for 2014, and my hacks at getting it shoehorned into a 2016 environment didn't get me far.
Scripts... To The Rescue Again
Well, if you can't use something pre-packaged, you turn to the script component, right? It's easy to "resort" to the script, because the script also solves for a few problems that custom objects don't: allowing 3rd party (black box) code to run inside your systems. At least you can code review (hah!) this stuff.
What I've got for you today is a semi-generic script - you will have some editing and setup to do - that connects (I think?) any version of SSIS to any version of SharePoint (until they deprecate WSDL). Of course, all those claims are unwarranted and untested - please do you own validation in your environment. There is a fair bit of code behind the scenes - unrepentantly cribbed from the aforementioned SharePoint List Adapter code on CodePlex, and other internet sources.
Please read through the commentary at the top of the script - you will have to set up the script to work for your server and list - adding some code and service references, and setting up the columns you want to retrieve.
Find the code here. All you'll need to do is:
- Drop a Script Component in your Data Flow, and say it's a "source"
- Set up some output columns
- Identify an HTML connection manager
- Alter the "Setup" part of the script as described in the script comments.
Understanding "Name" in SharePoint
Reflection - Accessing Script Columns Generically
Seeing ALL of the SharePoint Columns
Using Blobs for Really Wide Columns
Dynamic Reference to Web Services