Because I have no admin rights on the system where I need to work I can't use the SSIS OData Source. But I found a (complicated) work around:
Sharepoint lists export can be automated using Microsoft Flow. It is available directly in the browser when opening a Sharepoint list. Microsoft Flow could write directly into mssql, but this is a premium feature and I can't order. So I need to use the free options in Microsoft flow. A free option is to export a Sharepoint list into a CSV file. But the target will be in OneDrive. It would be possible to export to a network drive using a Flow gateway, but again I can't install because I have no machine running 24/7.
So I tried to go the complicated way:
- create a flow to daily export a Sharepoint list into CSV on OneDrive
- setup something to copy form OneDrive to a network drive (I still think about how to do to work every day not depending on my laptop)
- finally using AC to import the CSV files from a network share into the DWH
There are some issues with datetime fields which are exported in "ISO8601 with time zone Z. yyyy-MM-ddThh:mm:ss.fffZ (no spaces)". But this issue is not related to AC but it is a general issue that the Sharepoint lists I should import contain these fields. Depending on the last edit time (winter time or summer time) it is hard to get the original value shown in Sharepoint, because it would be required to know not only the last edit time and time offset of the whole row but also the time offset for each column which is not exported into CSV. For example when one value was created in summer time but the second column was changed in winter time.
It is complicated to copy data from one Microsoft product (SharePoint) into another (SQL Server) without admin rights on the local PC and without using Premium Features.
Maybe there are some easier ways?