OK, so we have written a utility (using Graph API) to allow us to authenticate and access files on our internal SharePoint online sites, to replace previous on-premise WebDav access, and we are having issues with the online files after they have been copied.
In simple terms, using Graph API, the process takes a file path from SharePoint Online, authenticates with the site and if the user has access to the file, copies it to a defined location. This works exactly as it should and the relevant files are correctly copied to the new location. For on-premise files, the location remains unchanged. The online files are copied as we can't find a way to integrate Graph API into SSIS, so the files need to have any permissions removed by copying them somewhere else.
We have an existing SSIS process which previously read from a list of on-premise file locations and imports the data from each Excel file into a SQL database. This worked without issue until we updated the list to include the SharePoint Online copied files. The SSIS process runs under the context of a service account, which also has read/write access to has defined location, but when the SSIS task runs, it returns with the following error:
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot update. Database or object is read-only.".
I've obviously searched for this error, and the only suggestions are to set IMEX=1 on the connection, which is already done, and to set DelayValidation = True, again this is already done.
The SSIS process should work the same for both the existing on-premise files as it does for the online ones, but for reasons unknown, the on-premise ones work as expected, but the online ones fail. The file isn't already open elsewhere, it's copied and then the SSIS process attempts to open it, but that's where it goes wrong. I can open the copied file without issue in Excel.
Is there something about SharePoint Online that leaves a lock on a file? Is there something we need to do using Graph API to release the copy from any links to SharePoint? Myself and my colleagues have all looked at this and can't figure out why the copied files don't open.
EDIT: Looking at the context of the SSIS error, it seems the ConnectionString is valid, as is the ExcelFilePath (which matches the Data Source in the former):
ConnectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\OUR_SERVER_NAME\ShareName_UAT$\Excel_Macro_Enabled_File.xlsm;Extended Properties="Excel 12.0 MACRO;HDR=NO";
ExcelFilePath: \\OUR_SERVER_NAME\ShareName_UAT$\Excel_Macro_Enabled_File.xlsm
Any ideas or advice gratefully received.
Thanks
Martin
