Hello @heta desai
To access an Excel document from SharePoint and directly dump its content into Azure SQL using Azure Data Factory, you can use the SharePoint Online List connector as a source and the Azure SQL Database connector as a sink.
However, please note that the Excel document needs to be in a supported format for Azure Data Factory to read it.
Here are the high-level steps to achieve this:
- Create a new data factory in the Azure portal.
- Create a new pipeline in the data factory.
- Add a SharePoint Online List dataset as the source for the pipeline. In the dataset, specify the URL of the SharePoint site and the name of the Excel document.
- Add an Azure SQL Database dataset as the sink for the pipeline. In the dataset, specify the connection string to the Azure SQL Database and the name of the table where you want to dump the data.
- Add a copy activity to the pipeline. In the copy activity, specify the SharePoint Online List dataset as the source and the Azure SQL Database dataset as the sink.
- Configure the mapping between the source and sink datasets. You can use the mapping options in Azure Data Factory to transform the data as needed. Once you have completed these steps, you can trigger the pipeline to read the content of the Excel document from SharePoint and directly dump it into Azure SQL.
Please note that if the Excel document is not in a supported format, you may need to convert it to a supported format before reading it with Azure Data Factory. Additionally, you may need to configure authentication for the SharePoint Online List dataset if your SharePoint site requires authentication.
I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.