Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Reading SharePoint Lists with Integration Services 2017


By:   |   Last Updated: 2018-07-11   |   Comments (49)   |   Related Tips: > Sharepoint

Problem

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?

Solution

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: https://mycompany.sharepoint.com/sites/mysite/_vti_bin/listdata.svc. 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 sharepoint.com 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.


Last Updated: 2018-07-11


next webcast button


next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, October 03, 2018 - 3:04:48 PM - Raol Back To Top

Hi,

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

Hi everyone,

the code was moved to Github. You can find it on the following link https://github.com/fan130/mssql-ssis-community-samples/releases

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

Regards,

Evandro.


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

Hi Ravi,

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

Regards,
Koen


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

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
</div>
16        16        <div class="ExternalClassF26B22F13B944489A1243E601971D971">Enhancement Severity should be defined</div>
16        16        <div class="ExternalClassCD75E7B47DF84D2D91531A9F83881BA8">Enhancement Description should be defined</div>



Thanks,
Manjunatha


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

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?

Koen


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

Hi Koen, 

I was able to resolve that issue. I ended  up using the url https://XXX.sharepoint.com/sites/CPT00330/_vti_bin/client.svc 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.

------------------------------

ADDITIONAL INFORMATION:

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)

------------------------------

BUTTONS:

OK

------------------------------

Regards,

Ravi


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


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.


Thanks,

Manjunatha


Wednesday, September 05, 2018 - 2:00:30 AM - Koen Verbeeck Back To Top

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.

Regards,
Koen


Tuesday, September 04, 2018 - 1:51:51 PM - Ravikiran Back To Top

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)
------------------------------
BUTTONS: OK
------------------------------


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

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

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

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

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 Back To Top

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

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

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 Back To Top

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 Back To Top

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 Back To Top

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

 

 

 


Wednesday, November 07, 2012 - 3:05:52 PM - Ray Barley Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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 Back To Top

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

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 Back To Top

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 Back To Top

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 Back To Top
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
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 Back To Top

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 Back To Top

The components have been tested to work with SharePoint 2010.


Tuesday, June 08, 2010 - 4:49:28 PM - KevinIdzi Back To Top

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

 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

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

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

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 Back To Top

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

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 Back To Top

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 Back To Top

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

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

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 Back To Top

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

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

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

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 Back To Top

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


Learn more about SQL Server tools