Forum Discussion
SSIS package and modern authentication
Did you ever get this figured out? I'm currently trying to set up a connection between SSIS ODataSource and our SQL server, but our admin is wanting to find a solution with modern authentication. Axel_Lemaitre1
hi donnie027 , According to this article
https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/odata-connection-manager?view=sql-server-ver15, ODataSource does not support modern authentication. I would suggest using Microsoft Flow in SPO to export Sharepoint List to Excel (shared path) and then import the Excel into SSIS.
- https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/744323
Hi tarunraghu55 ,
Currently we are connecting basic authentication and It's need to change share point connection from basic to modern authentication, You said OData Source doesn't support and suggest as as export SharePoint list to excel.
Could you please provide the details how it works and how is connect that SP list to excel.
Thanks,
SaihiSAIDULU333 ,
Below is the step by step process of how we did it (Read full process before implementing)
1) Create a Microsoft Flow in Sharepoint Online Site
i) recurrence step is to run this flow every 10 minutes
ii) get items step is to get data from Sharepoint list
iii) With the output of get items we create a CSV table
iv) We delete the CSV file created in the Sharepoint library (Create a dummy file in the library before doing this step , every Sharepoint site should have a Sharepoint Library)
v) we create the CSV file in the Sharepoint library using the CSV table step
we are doing delete and create as there is no overwrite option in Microsoft Flow
2) Setup a VM , in the VM connect to the Sharepoint Online site using a sys account (provide access to sys account as needed)
3) Setup One Drive Sync in the VM for the sys account , your One Drive Folder will behave as a shared path which will have the CSV file created by Microsoft Flow (it will be in sync with the Sharepoint site)
4) In your SSIS solution we should use Flat File as a source and refer to the CSV file present in One Drive Sharepoint path
This process is slightly complicated with a VM setup and Microsoft Flow but I was able to implement this successfully.
Let me know if this satisfies your requirement. We can discuss further if any issues.
