solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Accessing SharePoint Lists with SQL Server Integration Services SSIS 2005

MSSQLTips author Ray Barley By:   |   Read Comments (40)   |   Related Tips: > Sharepoint

Problem
I have a requirement to extract data from a SharePoint list.  Ideally I would like to be able to do this from an SSIS package.  In an earlier tip you showed how to do this by implementing a CLR function that invokes the SharePoint Lists web service.  Isn't there a built-in component that we could use to do this? 

Solution
There is a component available on the CodePlex site which should meet your requirements.  The SharePoint List Source and Destination Sample provides a Source adapter to extract data from a SharePoint list and a Destination adapter to update data in a SharePoint list.  In this tip we will walk through installing the CodePlex sample and creating an SSIS package to extract data from a SharePoint list using SSIS 2005.

Installation

Download the code for the SharePoint List Source and Destination Sample and launch the ,msi file to install the components.  Note that there are separate downloads for SQL Server 2005 and SQL Server 2008.  The components require the .NET Framework version 3.5; the installation will prompt you to download and install the .NET Framework if you do not have the version required.  If you choose to download and install the .NET Framework your browser will open and navigate to the download page.  As of the date of this writing you should be downloading and installing the .NET Framework version 3.5 Service Pack 1.  There is a link on the page to download the full package; this will allow you to download everything you need.  The default link will download a bootstrapper which will launch and download additional code as it runs.  By downloading everything you have what you need to install the .NET Framework on another machine if necessary.

After installing the SharePoint List Source and Destination Sample code, open the Business Intelligence Development Studio (BIDS) and add the new components to the Toolbox under Data Flow Sources and Data Flow Destinations.  Create a new SSIS package then click Tools on the top-level menu, then Choose Toolbox Items.  Click the SSIS Data Flow Items tab and click the checkboxes for SharePoint List Destination and SharePoint List Source.  After clicking OK on the dialog, you should now see SharePoint List Source under the Data Flow Sources and SharePoint List Destination under the Data Flow Destinations in the Toolbox.  We are now ready to create a sample SSIS package that extracts data from a SharePoint list.

Create a Sample SSIS Package

Add a Data Flow component to the Control Flow of a new or existing SSIS package, then add the components below to the Data Flow:

The SharePoint List Source can be found in the Toolbox under the Data Flow Sources.  To configure the component, right click on it and select Edit from the popup menu.  You will see the dialog below:

For this example you need to select a list in a SharePoint site.  In my case I have a site called AdventureWorks that has a standard Contacts list that includes all of the employees from the DimEmployee table of the AdventureWorksDW database that comes with SQL Server 2005.  After you pick a SharePoint list, fill in the SiteUrl and SiteListName as appropriate.  The URL of my sample list is: http://bi-wss/adventureworks/Lists/Contacts/AllItems.aspx - use this as an example of how to extract the SiteListName and SiteUrl based on the URL of your list.  Note that there is a SiteListViewName property where you can specify the name of a view that you have created for your list.  The view allows you to specify the field list, sorting, etc.  For our example we'll just leave it blank and go with the default view for the list.

As you can see in the above edit dialog for the SharePoint List Source there are two additional tabs - Column Mappings and Input and Output Properties.  You can accept the default values.

For our example we'll write out the SharePoint list contents to a flat file.  Add a Flat File Destination and configure its Flat File Connection Manger (named FlatFileOutput) to point to the file of your choice on your local hard drive; e.g. "c:\mssqltips\ssis_sharepointlists\contacts.txt".  You may want to check "Overwrite data in the file" on the Flat File Destination's Connection Manager dialog so that the file will be overwritten if it already exists.  In my case I set the column delimiter to Tab {t} on the Flat File Connection Manager's Columns page since my data has a FullName field with a comma separating the last name and first name.

We are now ready to run the sample SSIS package.  Right click on it in the BIDS Solution Explorer and select Execute Package from the popup menu.  You should see the package execute and write out the contents of your SharePoint list to a text file as specified in the FlatFileOutput connection manager.

One final note - you may have noticed the CamlQuery property on the SharePoint List Source Component Properties dialog shown above.  CAML stands for Collaborative Application Markup Language.  It is XML and in this case you could use it to specify sorting and filtering on your list.  You can get more details on the CAML Query element here.

Next Steps



Last Update: 4/20/2009


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, December 18, 2012 - 11:05:05 AM - Vinny Read The Tip

Still stuck on my issue but I discovered it's not due to claims.

 

In my SP environment I have 2 Claims Auth Types setup under my default zone - Enable Windows Auth is checked and Integrated NTLM is choosen.  In addition I have enabled Forms Based Auth provider/role set up as well.  With these options set I receive an error "The HTTP request was forbidden with client authentication scheme 'Ntlm'.

 

I then went to change the binding from .ntlm to .windows

binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows

 

and then I receive The HTTP request was forbidden with client authentication scheme 'Negotiate'

 

I noticed if I disable the FBA setting it works!  Is there a way around this where I can set the FBA setting?


Wednesday, November 28, 2012 - 2:36:48 PM - Ray Barley Read The Tip

I haven't seen one.  

Claims authentication is a SharePoint topic so you're probably going to have to start looking to some SharePoint resources.


Wednesday, November 28, 2012 - 1:24:05 PM - Vinny Read The Tip

I think I figured it out.  I ended up taking the samples.snk from one of the other projects and copying it to the SharepointListAdapters project and that worked. 

Now, I'm still having the issue with the 401 error.  Not sure what else to try.  Is there a code sniplet somewhere that I can use to help me allow it to work for claims based sites?


Wednesday, November 28, 2012 - 10:14:09 AM - Ray Barley Read The Tip

I think you have to remove the original component from the SSIS toolbox in Visual Studio then add the new version of the component to the toolbox.

If you need directions on how to do this, go to http://msdn.microsoft.com/en-us/library/hh368261.aspx and click the link to download the Word document Extracting and Loading SharePoint Data in SQL Server Integration Services.  You will find the details there.  To remove a component from the toolbox I assume you just right click and select delete.


Wednesday, November 28, 2012 - 9:30:42 AM - Vinny Read The Tip

Ray, thanks for the help!

It appears the publickeytoken is not the same after I rebuilt.  SharePointListAdapters Public Key Token is now 23ba601cfa95663.  The original was f4b3011e1ece9d47

When I try to add the List Adapter it could not load the assembly and specifies the original PKT.  How can I change this or make it recognize the new PKT?

 

 


Tuesday, November 27, 2012 - 9:24:15 PM - Ray Barley Read The Tip

You have to put the SharePointAdaptersList.dll in 2 places: e.g. C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents and the global assembly cache (GAC)

Use the command GACUTIL /if {PATH}\SharePointAdaptersList.dll

You run GACUTIL from the command prompt; it may not be in your path; if not search your hard drive and you should find it; e.g. I found it here:

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\x64


Tuesday, November 27, 2012 - 4:34:45 PM - Vinny Read The Tip

I noticed the msi from codeplex does not work for Claims based sites.  So there was a solution to rebuild it and correct the .ntlm with .windows in the code.  http://sqlsrvintegrationsrv.codeplex.com/workitem/18463?FocusElement=CommentTextBox

I'm having problems with the build.  I tried to replace just the SharePointAdaptersList.dll with the current one but that does not work when you add the SSIS adapter shape.  It complains that it did not install properly.  

how can I rebuild this after I make my code changes?  I tried using the VS2010 Setup project but it installs in a different place.  I'm not sure what are the required files that I will need for the build.  Got any advice?


Thursday, November 08, 2012 - 7:39:55 AM - Ray Barley Read The Tip

There are usually 2 things that go wrong - an issue with security or the siteurl / sitelistname.  Make sure you can access the list from your browser; e.g. in the tip the url of my list is http://bi-wss/adventureworks/Lists/Contacts/AllItems.aspx  When you configure the SharePoint list source you would have siteurl = http://bi-wss/adventureworks and sitelistname Contacts.

You might also remove the SharePoint list source from your data flow and add it back.  Maybe there is an issue with the component itself that has happened based on the sequence of things you did.

Another thought is try not using the SharePoint credential; just connect to SharePoint using the credential that's running the SSIS package.  The password doesn't get saved for you automatically; you will need to take care of this yourself.  

Lastly go through the documentation that comes with the component; download the Word document Extracting and Loading SharePoint Data in SQL Server Integration Services on this page: http://msdn.microsoft.com/en-us/library/hh368261.aspx


Wednesday, November 07, 2012 - 6:53:24 PM - kim Read The Tip

getting error message when I am trying to map under input/output properties

  The extrnal metadata collection on "output" Output  is marked are not used- so no operation can be performed.

 and also it says - doesnot allow the insertion on external metadata column.

 

thanks


Wednesday, November 07, 2012 - 5:43:55 PM - kim Read The Tip

Thanks a lottt- I see these 2  souces.. keep u posted..

 

 

 


Wednesday, November 07, 2012 - 3:05:52 PM - Ray Barley Read The Tip

After opening an SSIS project in BIDS, click View on the top menu then Toolbox (if the Toolbox is not visible).

Right click anywhere in the Toolbox and click Choose Items from the popup menu.  You will see a bunch of tabs.  You can add SSIS-specific components by clicking the SSIS Data Flow Items tab or the SSIS Control Flow Items tab.  On either tab you will see SSIS components that have been installed on your system.  Each component has a checkbox next to it; check it to show the component in the toolbox.


Wednesday, November 07, 2012 - 2:38:16 PM - kim Read The Tip

I have installed this tool on my sql server box  and sharepoint is running under diff. box.

after intallation - I did created one sharepoint credential (from new connection) - but I dont see any thing in data flow sources or data flow destinations.

pls. advise.

thanks in advance


Wednesday, November 07, 2012 - 1:55:59 PM - kim Read The Tip

Hi Ray,

do we need to have wss or sharepoint on the same machine  where we have sql server and this tool installed?

I have sharepoint and sql server on separate machines and I dont see the new sharepoint task under dataflow.

thnaks

 


Friday, November 02, 2012 - 9:41:34 AM - Ray Barley Read The Tip

Choosing between saving your InfoPath form data to a SharePoint library or using a web service depends on what you need to do with the data.  For instance if you collect data on an infopath form and need to report on that data on demand then the web service approach where every time you create or update a form the data gets saved in a relational database may be a better choice.  Web service generally requires that you write some code.  You can more easily just store infopath data in a SharePoint library.


Friday, November 02, 2012 - 1:30:39 AM - ozbroomy Read The Tip

I used this tool in a project 2 years back with great success. It extracts data from an InfoPath Forms Libarary and merges (using SQL Megre command) it to a DB for reporting puposes. I am surprised to see it's still being suggested as there is no better or cheaper (hard to beat free!) alternative. Or is there? I am faced with a similar project and I am wondering if I should start by created a DB first and then the InfoPath Form and send data to SQL as mentioned here - http://blogs.msdn.com/b/infopath/archive/2007/01/29/submitting-to-a-database-via-web-services-in-infopath-forms-services.aspx. I'd love to hear your suggestions on this Ray.


Monday, August 13, 2012 - 10:01:58 AM - Ray Barley Read The Tip

After opening an SSIS project in BIDS, click View on the top menu then Toolbox (if the Toolbox is not visible).

Right click anywhere in the Toolbox and click Choose Items from the popup menu.  You will see a bunch of tabs.  You can add SSIS-specific components by clicking the SSIS Data Flow Items tab or the SSIS Control Flow Items tab.  On either tab you will see SSIS components that have been installed on your system.  Each component has a checkbox next to it; check it to show the component in the toolbox.


Friday, August 10, 2012 - 1:08:50 PM - Joe Read The Tip

Anyway to make the 2nd paragraph under installation a little clearer?  Not sure how to update the toolbox with instructions given.


Monday, April 09, 2012 - 11:54:37 AM - Ray Barley Read The Tip

It looks like Basic authentication is not supported in the original component that is out on CodePlex.  However read through this thread and you may be able to use the modifications that someone has put together:

http://sqlsrvintegrationsrv.codeplex.com/discussions/56119


Monday, April 09, 2012 - 10:45:06 AM - Daniel Read The Tip

Great Article! 

How can I set my SSIS package SharePoint list adapter to use 'Basic'  authentication instead of NTLM?  

It seems SSIS is trying to connect using NTLM,  however, it the SharePoint server wants to use 'Basic'.  This is part of the error I see:
unauthorized with client authentication scheme 'Ntlm'.   The authentication header received from the server was 'Basic'

I saw lots of posts with this (The authentication header received from the server was 'Negotiate,NTLM'),  but that is apparently not my
situation.


Wednesday, February 15, 2012 - 7:24:03 PM - Ray Barley Read The Tip
Did you add the new components to the Toolbox under Data Flow Sources and Data Flow Destinations in Business Intelligence Development Studio (BIDS)? I think you right click in the toolbox then navigate to the DLL if I remember correctly.

Wednesday, February 15, 2012 - 11:18:54 AM - Yippy Read The Tip
Hi, I am trying move an existing SSIS package to a new server that has SharePointListAdapters component used, therefore this new server doesn't have SharePointListAdapters installed. So I installed the set up file from this website http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 Still the package fails with error Component "component "SharePoint" (1)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. Component "SharePoint" (1) failed validation and returned error code 0xC0048021. When I open the SSIS package itself, it seems like that componement doesn't exist. Is there something I must do to register this properly?

Tuesday, June 08, 2010 - 4:52:35 PM - KevinIdzi Read The Tip

The inserts and updates can be done together.  The only difference is if an ID value exists.  If you load a datasource with SharePoint items, it will have an ID, and if you join it to some data you want to add - without the ID - you would have (using Union or Join), you could just send that whole dataset to the destination for update and it will insert the new ones and update the existing ones.

 Hopefully this simplifies it and makes it less complicated.


Tuesday, June 08, 2010 - 4:50:22 PM - KevinIdzi Read The Tip

The components have been tested to work with SharePoint 2010.


Tuesday, June 08, 2010 - 4:49:28 PM - KevinIdzi Read The Tip

That's how SharePoint returns it.  You will have to filter that out either with a .net script task data flow (removing tthe #; and the quotes, or some other component. Generally you'll see a number first, then #; is a delimiter, then the string after - which SharePoint puts into quotes (for the person's name in this case). In your case, that field is probably a dropdown I'm guessing, so the "Poirer,Rob" is some sort of looked up value.


Monday, May 24, 2010 - 11:14:02 PM - raybarley Read The Tip

 Probably the best you can do is write a function in T-SQL that parses the string and eliminates the unwanted characters.

 


 


Monday, May 24, 2010 - 9:31:42 PM - robpoiri Read The Tip

Well I got this to work with SharePoint 2010 and SQL Server 2008 R2 - almost.  Everything comes into SQL, except text fields which all come in with  #; in front of them.  For example #;"Poirier,Rob".

 

Any thoughts?


Monday, May 10, 2010 - 12:16:47 PM - raybarley Read The Tip

You're on the cutting edge!  Also no mention on CodePlex whether the components work with SharePoint 2010.  Based on your experience the answer may be no or maybe just not yet.


Monday, May 10, 2010 - 12:11:17 PM - robpoiri Read The Tip

I did create a new SSIS project and package in VS 2008.  In addition I am trying to connect to a SP2010 site to get the list data.

 

Too many new bits?[quote user="raybarley"]

I'm not sure if you did this but since you have a newer version of SQL Server (the original tip used SQL Server 2005) you should create a brand new SSIS package using SQL Server 2008 Business Intelligence Development Studio (or Visual Studio 2008).  I don't see any mention on the CodePlex site about SQL Server 2008 R2 so I don't know if there's any issue with it and the CodePlex SharePoint components.  

[/quote]

Monday, May 10, 2010 - 11:45:53 AM - raybarley Read The Tip

I'm not sure if you did this but since you have a newer version of SQL Server (the original tip used SQL Server 2005) you should create a brand new SSIS package using SQL Server 2008 Business Intelligence Development Studio (or Visual Studio 2008).  I don't see any mention on the CodePlex site about SQL Server 2008 R2 so I don't know if there's any issue with it and the CodePlex SharePoint components.  


Monday, May 10, 2010 - 10:50:30 AM - robpoiri Read The Tip

Hi -

 

I'm trying to use this package with SQL Server 2008 R2.  I downloaded and installed the .msi that was not designated as the SQL 2005 version.  I was able to add the items to the data flow toolbox, however when I test the component it throws an exception in SOAP, something to do with the Proxy.

 Do we need an update of the sample to work with 2008R2?

 

Thanks!


Monday, April 19, 2010 - 10:35:08 AM - raybarley Read The Tip

You need to install the SharePoint List Source and Destination components onto the server where you will run your SSIS packages; in yourcase that would be the server where SQL Server and SQL ServerAgent are running.  You get the installs from this location on CodePlex:

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

When you run a SQL Server Agent job that executes an SSIS package, the best practice is to create a credential (where you specify a Windows account and password) then setup a proxy (under SQL Server Agent) that uses the credential andyou specify the type of SQL Server Agent job steps that the proxy can execute.  You can then specify the proxy in the Run As dropdown in your job step.  This allows you to control the Windows account that will be used to execute your SSIS package.

If you don't create the credential and proxy then your SSIS package will run as the SQL Agent Service account.

Either way you go the account that your SSIS package runs as will need access to the SharePoint list.

I haven't seen any document that details anything about installing the SharePoint List Source and Destination components.

 


Monday, April 19, 2010 - 8:38:22 AM - Marzipan Read The Tip

In order to run a deployed SSIS package with the sharepoint adapters from a SQL server job, what needs to be installed on the SQL server.  Will running the adapter install on the server work?  Also, does the sqlservermgr account need access to the sharepoint list?  Any document outlining the steps?


Tuesday, February 23, 2010 - 2:29:43 PM - ChristianBahnsen Read The Tip

Thanks for the quick reply.  I'll open a trouble ticket with our help desk.


Tuesday, February 23, 2010 - 2:20:18 PM - raybarley Read The Tip
The error message you posted said that the HTTP request was forbidden for client authentication scheme NTLM. This is something you will have to handle on your end. The adapter should allow you to populate a list that you can use as a source for KPIs but not until you resolve the NTLM error.

Tuesday, February 23, 2010 - 1:56:59 PM - ChristianBahnsen Read The Tip

Ray,

Thanks for posting the tip on SharePoint List Source and Destination adapters.  I followed the instructions but they're not working for me.  I've opened a thread in the MSDN SharePoint Developer Center http://social.msdn.microsoft.com/Forums/en-US/sharepointbi/thread/b1b5c334-4d17-4a1c-b191-b53ff8c281e2 then came across this thread and decided to follow up here, too.

As noted in that thread, I work at a DOD (Department of Defense) site, so our security barriers may be higher than normal.  I've posted screenshots of the error message at http://www.christianbahnsen.com/sharepoint_source_error_message.pdf

If I can get this to work I think it would be very useful.  I'd played around with KPI Lists, but one cannot create an Indicator directly from SQL Server 2008 (cf. my related thread at http://social.msdn.microsoft.com/Forums/en/sharepointbi/thread/bcc95f65-012e-4b8a-8850-b03fb0f16b0f).  I was hoping that these adapters would provide a work-around by populating sharepoint lists that could be the source for KPI Indicators.

Thanks in advance for any assistance.

 Christian Bahnsen


Sunday, January 31, 2010 - 4:32:55 PM - elmogy_83 Read The Tip

Yes it souns complicated for me to extract the sharepoint lists and figure out the new items because i'm new with Integration service ... i think the Event handler is the best soultion  After adding the items in the offline list will added to the online list.

 Thanks a lot for your help and your time :) (F)


Sunday, January 31, 2010 - 10:36:21 AM - raybarley Read The Tip

Based on my understaninding of how the component works, you can't combine inserts and updates into a single operation.  I think the best you can do would be to extract the items from the destination list, extract the items from the offline list, then use SSIS to figure out which items in the offline list are new and which items are updates.  Finally you would have two SharePoint List destination components in a data flow, one would handle the inserts and the other the updates.

If this is starting to sound complicated then maybe SSIS isn't the best tool for this task.  SharePoint has an object model and web services that may work better for what you're trying to do.  SharePoint also has event handlers where you can specify code that you want to run when certain events happen on a list such as inserts or updates. 


Sunday, January 31, 2010 - 9:39:55 AM - elmogy_83 Read The Tip

Thanks For Quick Reply , i read this Article before and got the result i explained in my Question ...

First : there are only Tow option in Batch type one for modification and other for delation .

Second , i understand from this article that if you didn't assign the ID as output coulmn the inserting opertion will execute but the update opertion will not execute ... in other words the all items will reenter again with new IDs (Dublicatd Items)

the only thing i need is combine the inserting new items and update old items in one opertion.

Thanks in advance

 

 


Sunday, January 31, 2010 - 9:22:28 AM - raybarley Read The Tip

You can find the details on here: http://msdn.microsoft.com/en-us/library/dd365137.aspx

In particular take a look at the section titled "Setting the Properties of the SharePoint List Destination".  There is a property called BatchType.  You can set it to Create a Row, Update a Row or Delete a Row.  So it looks like you will have to separate your input into what type of operation then have a separate SharePoint List Destination for each one in your SSIS package.


Sunday, January 31, 2010 - 7:38:09 AM - elmogy_83 Read The Tip

Dears,

I 'm new with Integration service, and i try to create an offline sharepoint list to let the customer entring the data offline then apply all changes he did to other sharepoint list with the same structure as offline one.

I have alrady sharepoint list has data on sharepoint site , i got a template from this sharepoint list included the content then create new sharepoint list from this template then  create new integration service package and drag sharpoint list source and destintion from toolbox to control data flow tab and follow the instruction to set the porperties for source and destintion.

i want to update the old data and insert the new data from the sharpoint list i created from the template to the old one i have.

after read micrososft articles in this matter, i reach to

If i assign the ID to ID the data updated successfully, but if i assign ID to Null the new item inserted but this approche ReEntering the old data again !!

could you please guid me to suitable approche to bind the update the old data and insert the new one?

Thanks in advance.

Qassem , Sharepoint Developer




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.