Reading SharePoint Lists with Integration Services 2017

By:   |   Comments (69)   |   Related: > SharePoint


We have data stored in SharePoint lists, which we need to extract into our database. We need to use SQL Server Integration Services (SSIS) for this task. How can we achieve this using the built-in components?


In previous versions of SSIS, the open-source SharePoint List adaptors from Codeplex could be used to access data stored in SharePoint lists. This was described in a previous version of this tip, however, Codeplex has been deprecated by Microsoft and it seems there are no further updates for the SharePoint List adaptors. This means we have to use an alternative. For reading data from a SharePoint List, the OData Source component can be used since a SharePoint List is exposed as an OData feed.

The availability of the OData source component depends on the version of SSIS:

In this tip, we’ll read data from a SharePoint Online list using SSIS 2017. You can download the latest version of SQL Server Data Tools for Visual Studio 2017 here.

Test Data

First, we need a SharePoint List to read from. In this tip, we’ll use SharePoint Online, but the process is the same for an on-premises SharePoint. In the SharePoint site, navigate to Site Contents. There, you can add a new list by clicking on the plus symbol and choosing List from the dropdown.

create new list

You’ll need to specify a name and an optional description. You can also choose if the list is displayed in the navigation menu at the left.

specify list properties

When the list is created, a column with the name Title will be added by default. You can click on the plus icon on the right of the Title column to easily add new columns.

newly created list

We’ll add two extra columns: a code column containing integers and a text column. First, we’ll add a Number column:

add column

The column has the following properties:

ID column

Next up is the text column.

add text column

It has the following properties:

text column properties

Now we’re going to get rid of the Title column. In the top right corner, click on the arrow at All Items and select Edit Current View.

remove column

Deselect the Title column from the list of columns and click OK:

remove column

This removes Title from the view, but it’s still there and it requires values. Trying to insert data will result in an error if no value was specified for the Title column. We can change this behavior in the Settings. Go back to editing the view. At the top, click on Settings. This will take you to the list settings.

go to settings

In the columns section, click on Title.

go to settings for the title column

In the properties, set the setting for requiring information to No:

disable require information

Click OK and go back to the list. Now you can click on Quick Edit to fill in some data.

enter data in the list

Reading the List

Add a data flow to your SSIS package. From the Common section, drag the OData source into the canvas. Be careful, it is not listed under sources!

odata source

Open the editor. Click on New to create a new OData connection manager.

create new conn mgr

Specify a name for the connection manager and the URL for the SharePoint website. The  URL takes the following format: The parts in bold need to be replaced for your situation. If you use an on-premises installation of SharePoint, you’ll also need to replace with the address of you SharePoint website.

specify conn mgr properties

Depending on your scenario, there are different authentication options available. Since we’re using SharePoint Online in this tip and we want to specify a username and password (so it can maybe be parameterized later on), we’ll choose Microsoft Online Services. In order for this authentication type to work on the server running SSIS, you need to install the SharePoint Server 2013 Clients Components SDK. You need to install those libraries on every server running the SSIS package.

Once you selected the Microsoft Online Services option, you can specify a username (most likely an email address) and password.

specify username and pw

Click on OK to create the connection manager. Now we can choose our SharePoint list from the Collection dropdown:

select list from collections

In the OData Source component, you can work with Collections or Resource Paths. To read from a SharePoint list, collections are the easiest choice. For more info on resource paths, you can check out the tip Using the OData Source in SQL Server Integration Services. Click OK to close the editor. With a data viewer, we can inspect the data coming out of the SharePoint List:

data viewer

There are two issues:

  • All of the columns of the list are retrieved: our two columns, but also the Title column and all other metadata columns (some columns names are dependent on the regional settings, so they can differ from your situation)
  • The data types of some columns are not ideal. The Description column for example has the DT_NTEXT data type, which is a Unicode BLOB, which is bad for data flow performance.

In the next sections we’ll solve both problems.

Reducing the Number of Columns

You can specify which columns you want to retrieve in the source editor by using the query options:

odata query options

By using the select query option, you can select only the columns you actually need, which will also improve data flow performance. Keep in mind that having spaces in column names can cause issues.

limited columns

Converting the Data Types

Sometimes the data types returned by the OData source component are not ideal. Although the Description column in the SharePoint list is limited to 255 characters, the data type in the SSIS data flow is DT_NTEXT. Unfortunately, this data type cannot be converted using the Data Conversion transformation. Luckily, we can change the data type in the advanced editor of the source component. Right-click on the source component to open the advanced editor.

open advanced editor

In the editor, go to Input and Output Properties. There you navigate to Output > Output Columns in the tree in the left pane. When you select a column, you can change its data type.

change data types

We’re going to change the data type column to DT_WSTR with a length of 255. Keep in mind only DT_NTEXT and DT_WSTR can be converted in the advanced editor. Other data types need to be converted using transformations. If you try to convert other data types, you’ll get the following warning:

data type conversion error

When we run the data flow, we’ll get the following data viewer, where we can now finally read the data:

final result
Next Steps
Editor's Note
  • The original tip, "Accessing SharePoint Lists with SQL Server Integration Services SSIS 2005", was written by Ray Barley and published 2009-04-20.  The original tip referenced code on Codeplex that no longer exists, so this tip was rewritten on 2018-07-11 as shown above.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, September 13, 2021 - 12:07:24 PM - Mike Butterworth Back To Top (89227)
Hi all

I've been using this connection method for a while, but recently our server stopped getting a connection (error below). It's something to do with forcing TLS 1.2, and I've tried patching the server for .Net and forcing various registry entries but can't seem to get it right. Anyone had the same problem or any suggestions?

Running Server R2 2012, with SQL server 2017 and Visual studio 2017.

Many Thanks



The underlying connection was closed: An unexpected error occurred on a send. (System)


Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)


An existing connection was forcibly closed by the remote host (System)

Sunday, August 29, 2021 - 9:45:05 AM - Koen Verbeeck Back To Top (89187)
Hi Sean,

I don't have experience with all possible types of columns in the SharePoint list. I would try to extract all columns and see if you can find all the info you need (like the AD name). If it isn't there, you might need to do a lookup to AD.

You can try to read the separate list for your multivalued option and join it in the SSIS data flow?


Thursday, August 26, 2021 - 11:11:19 AM - Sean Back To Top (89172)
Thanks for the info which was very useful. I have two main issues that are affecting me and would appreciate any advise with this solution. I have a basic list set up with Name, AD Account Name and Codes set up in Sharepoint 2013 and extracting this list using SSIS.

Problem 1: The AD account name that I would need exports to my SSIS/new table as an ID, I really need the actual account name as it is linked with BI software.

Problem 2: People in my list can have more then one code as this is a multi value selection. I need all the relevant codes per people otherwise the solution is pointless. The SSIS collection seems to have done a separate list for my multivalued option and it does not appear like I have a join option that I can link back to my original list

Was considering Powershell to CSV to extract sharepoint list and CSV to SSIS if this was a better solution but was hoping there might be further guidance on this one. Appreciate the help all.

Tuesday, January 28, 2020 - 12:25:04 PM - Koen Verbeeck Back To Top (84019)

Hi Eric,

I'm afraid I haven't extracted forms data yet. Seeing the other comments in this thread, it seems you're not the only one with issues.


Tuesday, January 28, 2020 - 12:01:27 PM - eric81 Back To Top (84017)

I had an issue after upgrading from SQL 2012 to SQL 2016 the old codeplex SharePoint Adapter wouldn't work.  I then had to use OData Source data flow in SSIS, which worked for the most part.  One challenge I ran into was it doesn't allow for you to export from SharePoint list form views.   Well it does but the it seems it extracts the underlying metadata so for some columns it contained cryptic data.  Have you ever been able to use OData Source with SharePoint form views? 

Thursday, April 25, 2019 - 3:13:52 AM - Koen Verbeeck Back To Top (79707)

Some things to try:

* have only the source and maybe a multicast as a dummy destination. This way we can verify the problem is only at the source.

* try changing the buffer size to a very small size


Wednesday, April 24, 2019 - 10:32:12 AM - Kishore Nekkalapu Back To Top (79684)

Hi Koen,

There is one more error before the below one is.

[OData Source [5]] Error: The OData Source was unable to process the data. An error occured when reading the OData feed.



Tuesday, April 23, 2019 - 3:57:17 PM - Koen Verbeeck Back To Top (79664)


is that the only error that you get?


Tuesday, April 23, 2019 - 1:34:33 PM - Kishore Nekkalapu Back To Top (79661)

Hello All,

I am trying to read SharePoint List using Odata source from SSIS, when I configue the Odata source with the SharePoint List URL , I can able to see the preview and columns, but when execute the package its getting failed at source its self with the below error.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OData Source returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput().

As a next step tried to limit the source data by applying filter on Odata source and this it works well with 140 records, when run the package without filter its giving me the above issue and the complete record set has around 35000 records.

Please hel me to find the solution.



Tuesday, April 9, 2019 - 1:31:19 PM - Koen Verbeeck Back To Top (79509)

I think that as long as the URLs stay the same, it should keep working.


Tuesday, April 9, 2019 - 9:47:59 AM - vas Back To Top (79506)

Thank you Koen. I have installed in my DB and everything is running smoothly with BIDS 2010, SQL server 2012, and SharePoint 2013. The package is running using agent job, for which i have created a new agent account to run SSIS packages. Most of my packages sync with DB to SharePoint, and its working awesome! God bless the persons who created and support list adaptors!

I am adding new app server, will it affect anything?

Tuesday, April 9, 2019 - 8:22:05 AM - Koen Verbeeck Back To Top (79504)

Hi vas,

on the server that runs your SSIS packages.


Monday, April 8, 2019 - 2:08:10 PM - vas Back To Top (79499)

Which server should i install SharePoint list adaptors from codeplex in? We have two app, two front ends, and 1 db.

Wednesday, February 13, 2019 - 2:33:21 AM - Angela Sampedro Back To Top (79022)

Hello all,

Just to let you know that I got it working. I installed Sharepoint List Adapters which I added it to my SSIS Package plus an oData Source in the package as well. 



Tuesday, February 12, 2019 - 6:15:30 PM - Ravikiran Raghunath Back To Top (79018)

 Hi Angela, 

For that i was able to solve it by asking the sharepoint administrator to create a calculated column which would refer to those lookup column and display the value. This calculated column will be of basic type varchar so its easy to pull the data for this through odata adapter. 

Tuesday, February 12, 2019 - 5:24:54 AM - Angela Sampedro Back To Top (79011)

Hi Koen,

Thanks for your article. It helped me a lot today. However, I am running into the same issue as Ravikiran (14 September 2018)

My Sharepoint list has multiple choice and lookup columns (person or group).

I was wondering if you would have any advice please.



Sunday, February 3, 2019 - 10:02:14 AM - Koen Verbeeck Back To Top (78948)

Hi Ravikiran,

I haven't used the adaptors myself. You might want to try the Github site for support.


Friday, February 1, 2019 - 10:53:23 AM - Ravikiran Back To Top (78939)

 Thanks Koen for the quick response. I see those now on my Visual studio 2017. I added the Sharepoint credentials and added the SiteUrl, SiteListName and SiteListViewName settings and i get the error the http request is unauthorized with the client authentication scheme ntlm. the authentication header recieved from the the server was ntlm. the remote server returned an error 401 unauthorized. Is this something to do with the sharepoint online settings or do we have to change something on visual studio side. 

Friday, February 1, 2019 - 2:19:03 AM - Koen Verbeeck Back To Top (78935)

Hi Ravikiran,

check out the SharePoint List adaptor on Github:


Thursday, January 31, 2019 - 4:28:36 PM - Ravikiran Back To Top (78930)

Hi Koen,

I have an urgent requirement for a process to be able to write/delete/update items on sharepointonline list. I dont see any options for Odata Destination for the same. How do i go about this approach. I was able to successfully establish a ODATa source connection to read data. 

Do i need to install any third party addons such as Cdata( to do the same. 

Any advice by the community here. Plz!



Wednesday, October 3, 2018 - 3:04:48 PM - Raol Back To Top (77831)


Could you also please add an example on how to query "deep" structures in a sharepoint list like list/record/table objects?

Best regards

Thursday, September 27, 2018 - 9:23:57 AM - Evandro Muchinski Back To Top (77751)

Hi everyone,

the code was moved to Github. You can find it on the following link

By the way, great article! That is a good solution for reading a list :)



Monday, September 17, 2018 - 8:02:38 AM - Koen Verbeeck Back To Top (77620)

Hi Ravi,

I haven't tried the multiple choice type myself, so I'll have to test first.


Friday, September 14, 2018 - 1:15:39 PM - manjunatha Back To Top (77573)

Hi Koen,

Thanks for your quick reply.

Hereby I have attached -

Data types of the list view using in SharePoint:
Fieldname    Datatype
Commentary    Multiple lines of text
Created By    Person or Group
Modified By    Person or Group

Data types used in the SSIS data flow:
Unicode string [DT_WSTR]

Target table output
Created By    Modified By    Commentary
16        16        <div class="ExternalClassAB112929757D44839335737759AD05C7">Enhancement Priority should be defined
16        16        <div class="ExternalClassF26B22F13B944489A1243E601971D971">Enhancement Severity should be defined</div>
16        16        <div class="ExternalClassCD75E7B47DF84D2D91531A9F83881BA8">Enhancement Description should be defined</div>


Friday, September 14, 2018 - 9:50:54 AM - Koen Verbeeck Back To Top (77565)

Hi Manjunatha,

what are the data types of the list view you're using in SharePoint? (single line of text, number ...)

What are the data types used in the SSIS data flow?


Friday, September 14, 2018 - 9:23:41 AM - Ravikiran Back To Top (77564)

Hi Koen, 

I was able to resolve that issue. I ended  up using the url and with this i was able to establish a successfull connection. 

I used the resource path method with the GUID mentioned in the resource path like this ->  lists('E939BD42-5224-4220-A8E0-B0BA54462630')/items 

and the Query Options -> $select=Title, Project_x0020_Manager, Project_x0020_Type, OData__x0025__x0020_Complete, Start_x0020_Date, End_x0020_Date, Status,Project_x0020_Phase,Project_x0020_Type,Status0,IT_x0020_Senior_x0020_Manager,Total_x0020_Budget,Actual_x0020_Spent,Committed,Remaining,Comments

With the Query options, now i am unable to retrieve columns which are of type "Collection"/"Multiple Choice". How do i retrieve these parameters from the query options field. 

I read in some forums that its a known issue with SSIS OData adapter. Is it??

This is the error which i get for retrieving multiple choice fields. 

TITLE: Microsoft.Data.DataFeedClient


An error occured when reading the OData feed.



A null value was found for the property named 'Department', which has the expected type 'Collection(Edm.String)[Nullable=False]'. The expected type 'Collection(Edm.String)[Nullable=False]' does not allow null values. (Microsoft.Data.OData)







Friday, September 14, 2018 - 3:05:15 AM - Manjunath Back To Top (77558)

Hi Koen Verbeeck,

Using OData in SSIS package, able to fetch data from sharepoint and load to sql server table, I have few below issues -

1. sharepoint character data is converting to numeric

2. Getting HTML tags along with sharpoint data (

My sharepoint test data
), I need only string ' My sharepoint test data'

How to get charcter data and how to remove HTML tags

Appreciate your help on this.



Wednesday, September 5, 2018 - 2:00:30 AM - Koen Verbeeck Back To Top (77365)

Hi Ravikiran,

did you install the 32-bit or 64-bit client SDK?
I believe I installed the 64-bit one.

That's actually all there is to it. If this doesn't work, I suggest you contact Microsoft support.


Tuesday, September 4, 2018 - 1:51:51 PM - Ravikiran Back To Top (77362)

Hi Koen,

I am trying to configure a odata source in the new ssdt 2017 and am unable to do that. I am unable to find the additional option for MicrosoftOnlineservices Authentication which sets the property to true.

How do i get out of this error. Any suggestions would be appreciated. I have added all the necessary components for sharepoint including the client and odata package.

TITLE: OData Connection Manager Editor
Test connection failed
ADDITIONAL INFORMATION: The remote server returned an error: (400) Bad Request. (System)

Tuesday, December 18, 2012 - 11:05:05 AM - Vinny Back To Top (21014)

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 Back To Top (20606)

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 Back To Top (20604)

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 Back To Top (20593)

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 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 Back To Top (20589)

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 Back To Top (20577)

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 Back To Top (20571)

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.

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 8, 2012 - 7:39:55 AM - Ray Barley Back To Top (20258)

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:

Wednesday, November 7, 2012 - 6:53:24 PM - kim Back To Top (20255)

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.



Wednesday, November 7, 2012 - 5:43:55 PM - kim Back To Top (20254)

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




Wednesday, November 7, 2012 - 3:05:52 PM - Ray Barley Back To Top (20252)

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 7, 2012 - 2:38:16 PM - kim Back To Top (20251)

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 7, 2012 - 1:55:59 PM - kim Back To Top (20248)

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.



Friday, November 2, 2012 - 9:41:34 AM - Ray Barley Back To Top (20191)

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 2, 2012 - 1:30:39 AM - ozbroomy Back To Top (20181)

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 - I'd love to hear your suggestions on this Ray.

Monday, August 13, 2012 - 10:01:58 AM - Ray Barley Back To Top (19021)

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 Back To Top (18993)

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

Monday, April 9, 2012 - 11:54:37 AM - Ray Barley Back To Top (16825)

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:

Monday, April 9, 2012 - 10:45:06 AM - Daniel Back To Top (16822)

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

Wednesday, February 15, 2012 - 7:24:03 PM - Ray Barley Back To Top (16030)
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 Back To Top (16028)
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 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 8, 2010 - 4:52:35 PM - KevinIdzi Back To Top (5673)

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 8, 2010 - 4:50:22 PM - KevinIdzi Back To Top (5672)

The components have been tested to work with SharePoint 2010.

Tuesday, June 8, 2010 - 4:49:28 PM - KevinIdzi Back To Top (5671)

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 Back To Top (5497)

 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 Back To Top (5496)

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 Back To Top (5344)

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 Back To Top (5343)

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.  


Monday, May 10, 2010 - 11:45:53 AM - raybarley Back To Top (5342)

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 Back To Top (5341)

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?



Monday, April 19, 2010 - 10:35:08 AM - raybarley Back To Top (5261)

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:

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 Back To Top (5260)

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 Back To Top (4945)

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

Tuesday, February 23, 2010 - 2:20:18 PM - raybarley Back To Top (4944)
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 Back To Top (4942)


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 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

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  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 Back To Top (4826)

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 Back To Top (4825)

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 Back To Top (4824)

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 Back To Top (4823)

You can find the details on here:

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 Back To Top (4821)


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

get free sql tips
agree to terms