Why do I only get user's ID when I try to load from SharePoint all his personal informations into SQL using SSIS?

Question

I'm trying to load values from Sharepoint 2019 to SQL using SSIS.

I have users in my list and I'm trying to read them.

But when I load them into my SQL using SSIS, it only loads me the ID and not the entire user with his informations (name, mail, etc.)

So I tried to load them like this :

I'm using OData Source to load those values and inside the Workflow's URL I put this URL that contains my values as a XML :

https://my.website.com/sites/sales/_vti_bin/listdata.svc/RequestPurchase$expand=Requestor,HeadOfDepartement,UnitManager,CreateBy,ModifyBy`
When I access to this URL with my browsers I see all my information (users with mail, name, etc.) but it is when I load it into SSIS there's only the Requestor's ID.

What am I doing wrong ?

Answer 1

Hi @Pinto Raminhos, Tiago Filipe ,

Did you create some Inputs and Outputs with mappings for those columns(name, mail, etc.)? And those columns should be created in your SharePoint 2019 list before you load this list into SQL. Then you have to write codes to fetch the item from the list and insert into table.

Here is a article about How to Load Data To A SQL Table From SharePoint List Using SSIS step by step for reference.
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.