Data Transfer: SharePoint List Attachments
  Print This Page

Data Transfer > SharePoint List Attachments

Functionality

The SharePoint List Attachments reader retrieves files attached to individual list items in a SharePoint Online list and imports them into a Centralpoint module. It is a companion to the SharePoint List reader: the parent reader imports the list rows, and the attachments reader imports the files attached to those rows.

The two transfers are linked by Centralpoint's Following execution plan. When the parent transfer completes, the attachments transfer runs immediately afterward and pulls the files attached to each list item that was just imported.

Attachments can be stored two ways. By default each file is written to /Uploads/SharePointListAttachments and the record points at that path. An Uploads path carries no role membership, so the file cannot be restricted to particular roles and anyone who can reach the URL can open it. Where that is not acceptable, the AttachmentToResource CpScript stores each attachment in the Resources library instead, where access is checked against roles on every request. See Storing Attachments as Protected Resources below.

Prerequisites

Before configuring the transfers, the Azure AD App Registration that backs the Centralpoint authorization record must have two delegated API permissions granted, both with admin consent.

API Permission Used by
Microsoft Graph Sites.Read.All List item enumeration (parent transfer)
Office 365 SharePoint Online AllSites.Read Attachment retrieval and download (attachments transfer)

Microsoft Graph and SharePoint REST are separate APIs with different audience values. Centralpoint uses Graph to enumerate list items and SharePoint REST to retrieve attachments. A single admin consent in Azure covers both, because the refresh token issued by the consent is multi-resource.

Azure AD Setup

If the parent SharePoint List transfer is already working in the site, then Microsoft Graph: Sites.Read.All is already in place. The only new permission to add is SharePoint Online: AllSites.Read.

  1. Navigate to Azure Active Directory > App registrations and select the registration that backs the Centralpoint auth record.
  2. Open API permissions and click Add a permission.
  3. Select the APIs my organization uses tab and search for Office 365 SharePoint Online.
  4. Choose Delegated permissions.
  5. Add AllSites.Read and click Add permissions.
  6. Click Grant admin consent for {tenant} at the top of the permissions table. This step requires the Global Administrator, Application Administrator, or Cloud Application Administrator role.
  7. Confirm a green checkmark appears in the Status column next to the new permission.
  8. Return to Centralpoint and re-authorize the existing auth record. The Microsoft consent screen should list both Sites.Read.All (Microsoft Graph) and AllSites.Read (SharePoint). Without this re-authorization, the existing refresh token does not cover SharePoint REST and attachment downloads will fail with a 401 or 403 response.

SharePoint Online uses different permission names than Microsoft Graph. The delegated AllSites.Read on SharePoint Online is the equivalent of Graph's Sites.Read.All; the names are not interchangeable in the Azure portal.

Centralpoint Configuration

The attachments feature requires two Data Transfers configured as a parent/child pair.

Parent transfer (SharePoint List)

  • Source tab: set Authorization, SharePoint List URL, and SharePoint List Name to point at the source list.
  • Fields tab: map Centralpoint DataId (SharePointListDataId) to the cpsys_DataId attribute. This system-generated DataId is what links each list item to its attachments. If the mapping is omitted, the transfer still runs and imports list rows, but attachments cannot be retrieved by the companion transfer. A warning is logged to the transfer's status when this mapping is missing.
  • Destination: a Generic Enhanced module that will hold the list items.

Attachments transfer (SharePoint List Attachments)

  • Source tab: configure the same Authorization, SharePoint List URL, and SharePoint List Name as the parent transfer.
  • Fields tab: map Centralpoint DataId (SharePointListDataId) to cpsys_DataId. Map the LocalPath column to the destination's file attribute so the downloaded file is surfaced on the attachment record. To store the files as role-protected Resources instead, map the AttachmentToResource CpScript to that attribute in place of LocalPath.
  • Scheduled tab: set Scheduled to Following and set ScheduledAfter to the parent SharePoint List transfer. This guarantees the parent imports before the attachments transfer runs and gives the reader the information it needs to compute the link between each attachment and its parent record.
  • Destination: a Document Library-style module that will hold the attachment records.

Note on Show Source Sample: the Size column shows as null in the Fields tab's Show Source Sample preview. Sample mode skips the file download to keep the preview fast, and Size is populated from the file on disk after download. The column is populated normally at runtime; map it as expected.

Storing Attachments as Protected Resources

An attachment in /Uploads is readable by anyone who has or guesses its URL. The AttachmentToResource CpScript changes where the file lands: it downloads each attachment directly into the Resources library, stamps the roles and audiences you choose on the resulting Resource, and returns the /Resource.ashx link as the attribute's value. Access is then evaluated against role membership every time the file is requested.

Apply it on the attachments transfer only. The parent SharePoint List transfer is not touched.

  1. On the Fields tab, find the destination attribute that takes the LocalPath column.
  2. Change that mapping from the LocalPath column to a CpScript, and select AttachmentToResource. The script returns the link, so LocalPath is no longer mapped anywhere.
  3. Set Resource Roles to the roles that should be able to open these files.
  4. Leave the Centralpoint DataId (SharePointListDataId) to cpsys_DataId mapping in place. It is still required, and it is what ties each Resource back to its attachment record.

Written as a tag, the mapping looks like this:

AttachmentToResource

There is no separate setting to turn this mode on. The presence of the script in the field map is the switch: while it is mapped, the reader stops writing copies to Uploads because the script has taken over the download. Removing it returns the transfer to Uploads mode on its next run.

Script attributes

Attribute Default Description
Resource Roles Private Comma separated list of role system names (not IDs) allowed to open the Resources this transfer creates. Find them under Admin > Roles. Left blank, every Resource carries the Private role, which means any signed-in user can open it and anonymous visitors cannot; naming roles here restricts access further. That default is deliberate: a file moved out of Uploads for access control should not stay readable to anonymous visitors because the attribute was left empty. Where the destination website has Role Filtration And Security turned on, a user must hold every role listed rather than any one of them. A name that does not exist is treated as a configuration error rather than skipped, because silently dropping it would publish the document with different access than intended. Note that the identically named attribute on the FileDownloadToResources script takes role IDs rather than system names, and its Resource Url By System Name defaults off where this one defaults on.
Resource Audiences All Audiences Comma separated list of Audience IDs assigned to each Resource. Both {guid} and {guid}:1:0 are accepted, where the two flags are Is CDA and Is Console; a bare ID sets both.
Resource Url By System Name Yes Returns the /Resource.ashx?sn={systemName} form of the link, which stays readable and survives a re-import. Set to No to return the /Resource.ashx?id={resourceId} form instead. Leave this at the default unless a specific consumer requires the ID form.

The script may be mapped to more than one attribute; the file is downloaded once and the same link is returned to each. Give every mapping the same settings, though: an attachment becomes a single Resource, which can carry only one set of roles and audiences, so where two mappings disagree the first one the transfer evaluates decides and the others are reported as ignored in the status.

What changes when the script is applied

  • Resources are created. Each attachment becomes a row in cpsys_Resources with the chosen roles and audiences, plus a file under the destination website's Resources folder.
  • Files stop being written to Uploads. The reader no longer places a copy at /Uploads/SharePointListAttachments/{ListId}/{ItemId}/.
  • The matching Uploads directory is cleaned up, and stays clean. Once a Resource exists for every attachment the transfer tracks, it removes the public copies it has replaced at /Uploads/SharePointListAttachments/{ListId} and reports how many went. This is not a one-time migration step: the transfer keeps that directory clear on every run, so a copy left behind by an earlier configuration is picked up the next time it runs cleanly.
  • Records for departed list items start being removed. The transfer begins deleting destination records for list items that are no longer in SharePoint, or that no longer carry any attachments, along with the Resources belonging to them. Without the script those records are left in place, so this is a behavior an attachments transfer gains by being converted rather than one it already had.

The cleanup is deliberately cautious, because deleting the public copies is the one step that cannot be undone. It runs at the end of a transfer rather than the beginning, and only once every attachment the transfer tracks has a Resource standing in for it. A run cut short by LimitWrites or SkipWrites, aborted from the console, or carrying any failed write leaves every Uploads file exactly where it is and says why in the status. Resolve what it reports and run the transfer to completion; nothing is lost in the meantime.

Transfers that share a list

The Uploads path contains the list ID but no module ID, so two attachments transfers pulling the same SharePoint list into different modules write into the same directory. Deleting it for one of them would break the other's links. The reader checks for this: if another attachments transfer points at the same list and is still in Uploads mode, the run refuses with a message naming that transfer and nothing is deleted. Apply the script to every transfer that shares the list, then run them.

Roles are re-stamped on every run

The roles and audiences on each Resource are replaced from the script attributes each time the transfer runs, so a role added by hand in the Resources console is removed on the next run. The transfer owns these Resources; change access by editing the script attribute rather than the Resource.

When an attachment is removed

When an attachment disappears from SharePoint the Resource follows it, whether a single attachment was deleted from a list item, the whole list item was deleted, or the item remains but no longer has any attachments. The Resource row is deleted first and its file is removed only if that succeeded. Centralpoint refuses to delete a Resource that is still referenced by a versioned or archived record, which is expected rather than an error; the transfer reports which Resource was kept, leaves the file in place, and the run continues.

Troubleshooting

Symptom Cause and resolution
Status reads "ResourceRoles names not found in cpsys_Roles" One of the names in Resource Roles is not a role system name. The message lists the unresolved values. Correct the attribute and re-run; Resources written by earlier successful runs are unaffected.
Status reads "ResourceRoles was not set, so resources carry the Private role" Informational. The attribute is empty, so the files are readable by any signed-in user and blocked for anonymous visitors. Set it to the roles that should have access if you need it narrower.
Run refused, naming another transfer Another attachments transfer pulls the same list and still writes to Uploads. Apply the script to that transfer as well, then re-run.
Status reads "Legacy /Uploads files were left in place" Not every tracked attachment has a Resource yet, so the cleanup was skipped on purpose and the public files are intact. Clear the reported failures, remove LimitWrites and SkipWrites, and run the transfer to completion.
Files still appear under /Uploads after a clean run A file that could not be deleted is counted and reported in the status, and remains reachable at its Uploads URL without a role check. This is usually a file lock on the destination server. Remove it manually or re-run once the lock clears.
The destination attribute is empty for some records No Resource was written for that attachment, so the script had no link to return and the attribute was written empty for that run. The transfer status carries the reason once; the most common causes are an unresolved role name and an invalid audience ID. Correct the setting and run again and the values are restored, and the files under /Uploads are never removed while the value is unresolved.
The link resolves in one environment but not another Resources are stored on the website that owns the destination module, which is not necessarily the server running Master. Test against the site that owns the destination rather than a local copy.

Reverting to Uploads

Remove the script from the field map and re-map the LocalPath column to the file attribute. The next run writes files to Uploads again.

This is not a clean revert. Every Resource the transfer already created stays live and readable by its URL, and it is no longer removed when its attachment disappears from SharePoint, because the write and the cleanup are driven by the same script. Delete those Resources by hand in Design > Media Manager if they are no longer wanted.

Retrieving Attachments on the Front End

The system table cpsys_SharePointListAttachments is the bridge between an attachment record and the parent list item record it belongs to:

  • DataId: the DataId of the attachment record in the attachments destination module.
  • ParentDataId: the DataId of the parent list item record in the parent destination module.

To retrieve the attachments belonging to a specific parent list item record, join the attachments destination's cprel_* relation table to cpsys_SharePointListAttachments and filter by ParentDataId. The example below shows the full relation, including the parent destination's cprel_* table on the other side of the link:

SELECT *
FROM cprel_GenericEnhancedLL attachmentsModule
JOIN cpsys_SharePointListAttachments systemAttachmentsTable
    ON attachmentsModule.DataId = systemAttachmentsTable.DataId
JOIN cprel_GenericEnhancedJ parentListRowsTable
    ON parentListRowsTable.DataId = systemAttachmentsTable.ParentDataId
WHERE parentListRowsTable.DataId = '7026654c-f907-5a13-90e5-e655b05e3cec'

Replace cprel_GenericEnhancedLL with the relation table name of your attachments destination module and cprel_GenericEnhancedJ with the relation table name of your parent destination module. Replace the hardcoded GUID with the parent record's DataId, typically resolved at runtime from the current record via CpScript or a DataSource binding.

When attachments are stored as Resources

The join above is unchanged. The attachment records and cpsys_SharePointListAttachments work exactly as before; the only difference is what the destination attribute contains. Instead of an Uploads path it holds a ready-to-use Resource link, so the same query returns it and the front end renders it directly:

/Resource.ashx?sn=annual_report-5dee5124b9

The role check applies when the file is requested from the public site. Console users reach the Resource through the Resources library as they would any other document, where console permissions apply rather than the Resource's role list. Verify the gate by requesting the link while signed out.