Access XML file hosted on SharePoint using SSIS

Question

Hi All,

I would like to access an XML file hosted in a SharePoint folder, read it's content, and then move it to another folder also in SharePoint. This process should be developed using SSIS. My questions are:

1- How can I access this file from SSIS ? I know there is an XML Source component, but what path should I put ?

2- How to move the file to a different location in SharePoint as well ?

I would like to achieve this without third party tools.

Thanks & Regards

Answer 1

Hi @Khidir Elsanosi ,

1.Use the 'copy link' feature in sharepoint to generate a URL with authentication.
2.Use the 'execute process task' to download the file using powershell.
3.Import the file to SQL server using an 'xml source' connection found inside the data flow under 'other sources'.
Please refer to export data from a xml file on sharepoint to sql server using ssis.

We can also use OData Source to connect to sharepoint.
Please refer to How to configure OData SSIS Connection for SharePoint Online.

Best Regards,
Mona

----------

If the 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.