4

I am new to Sharepoint Online API and I would like to extract data from a Sharepoint list using a SSIS flow. I tried to do this using OData Source, using the URL:

http://name.sharepoint.com/sites/l/_vti_bin/ListData.svc but I am not able to see all the properties of list items.

So now I would like to try using the REST API:

http://name.sharepoint.com/sites/l/_api/web/lists. but the issue is that this link is not working in SSIS.

Is there any possibility to do this using SSIS objects (not code)?

Hadi
  • 36,233
  • 13
  • 65
  • 124
alex
  • 41
  • 3
  • Hi Alex. Which properties were you not able to retrieve through the ListData.svc endpoint? And did you include any query options to specify which fields you wanted it to return? – Thriggle May 20 '19 at 20:17
  • I didn't exclude any field as I only chose the Collection and I let the query options empty. In the list there is a field which has the type = Collection(Edm.Int32). This is the field that I am not able to see through ListData.svc but it's visible when I use _api/web/lists. – alex Jun 01 '19 at 10:09
  • If you invoke the ListData service without explicitly specifying which fields to return through the $select query option, you're not guaranteed to get all fields; just common ones (e.g. Title, Created, ID) and an unspecified number of custom ones. The downside of using the $select option is that you'll only get the fields you specify explicitly, but this shouldn't be a problem if you know what fields you need. The newer REST API works similarly, with the same $select query option to return only the field you need. – Thriggle Jun 01 '19 at 17:24
  • Using ListData service the $select works and it's returning all the custom fields except that specific custom field.Using newer REST API the $select returns all the fields including this one. – alex Jun 02 '19 at 07:59

1 Answers1

1

You can simply use the Sharepoint List adapter components which are an open source project created by SQL Server community within a project called MSSQL SSIS Community:

You can check the project page at the following link:

Or you can simply download the assemblies from the link below:

You can read more about this components in the following link:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi @Hadi How do you install that component? I downloaded the zip file from github, couldn't find any guide on installation though – Ariox66 May 17 '21 at 23:50
  • @Ariox66 check the [following article](https://www.dimodelo.com/blog/2011/how-to-add-a-custom-ssis-component-to-visual-studio-tool-box/). Noting that backslashes are not shown in the folder path. i.e. `C:Program Files\Microsoft SQL Server\100\DTSPipelineComponents` – Hadi May 18 '21 at 18:04