export data from a xml file on sharepoint to sql server using ssis

2
votes

I have a xml file sitting in a folder which is in a document library on a page on sharepoint. I am trying to extract data from the xml file to a table using SSIS packages.

I have tried using Listadapters for sharepoint and I don't think that works to export data from a file on sharepoint library (maybe only for lists).

I have also tried using HTTP connection manager to connect to the sharepoint online. But it keeps giving me an error saying "connection is forbidden (403). I have provided the appropriate credentials.

This is my first time and any kind of direction would be appreciated.

1
sql-serversharepointssis

1 Answers

0
votes
  1. Use the 'copy link' feature in sharepoint to generate a URL with authentication.

enter image description here

  1. Use the 'execute process task' to download the file using powershell

enter image description here

  1. Import the file to SQL server using an 'xml source' connection found inside the data flow under 'other sources'.