none
Get user name from SharePoint Online list in SSIS package

    Question

  • I'm building a database in SQL Server to build reports based on SharePoint Online sites. On the sites I have several columns refering to users, such as "Assigned to", "Owner" and that kind of person column. When reading those lists in OData source, I get the actual value which is the ID of the user in the site however I cannot find a way to get the user name.

    In a REST query I know I can use $expand to get those values but that does not seems to work inside SSIS.

    Is there a way I can retrieve the list of users of a given site so I can join them with my lists?

    Thanks in advance.


    Rene Alvarez

    Wednesday, September 7, 2016 12:47 AM

Answers

All replies

  • Hi Rene,

    I do not think SSIS needs to be used to create reports off SP.

    You need to use SSRS in my view.


    Arthur

    MyBlog


    Twitter

    Wednesday, September 7, 2016 1:13 PM
  • Hi René Alvarez,

    It would be more flexible to retrieve that kind of data via Sharepoint Online REST APIs in C#, so I moved your thread to a dedicated forum for better result. Thanks for your understanding.


    Eric Zhang
    TechNet Community Support

    Thursday, September 8, 2016 2:07 AM
  • Hi Rene,

    Please check the following REST URI to query:

    /sites/dennis/_api/web/lists/getbytitle('CustomList8')/items?$select=Title,Owner/Title&$expand=Owner/Id

    Or we can use .NET Client Object Model(C#) to get the list data from SharePoint Online. The following articles for your reference:

    How to retrieve list elements from a SharePoint Online list programatically

    http://geekswithblogs.net/ThorvaldBoe/archive/2015/07/07/how-to-retrieve-list-elements-from-a-sharepoint-online-list.aspx

    How to get Sharepoint User objects from AssignedTo field by CSOM

    https://code.msdn.microsoft.com/office/How-to-get-Sharepoint-User-8570319c

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, September 8, 2016 9:49 AM
    Moderator
  • I encountered this problem too, although I found a UserInformationList (_vti_bin/ListData.svc/UserInformationList) that contained the mapping between IDs and user information, so brought that in via SSIS as well and then matched on IDs.

    One thing to be aware of is that this list is likely to be different per Site Collection so need to keep that in mind if you are drawing in data from various Site Collections to bring in corresponding UserInformationList.

    Hope that helps! 

    Tuesday, May 2, 2017 11:38 PM