Automating File Management in Dataverse Using PowerShell & SharePoint

 

Overcoming Dataverse FileType Limitations with Persistence & Debugging

Managing files in Microsoft Dataverse can be tricky, especially when dealing with FileType columns. Unlike traditional attachments, FileType columns cannot be migrated using standard tools, making automation a challenge.

I recently worked on an automation process that required retrieving files from SharePoint and uploading them into Dataverse. What seemed like a straightforward task turned into a deep debugging exercise due to the way Dataverse handles FileType columns.


The Challenge

Dataverse supports two types of file storage:

  • Notes Attachments (annotation) – Can be migrated with tools like Power Automate and KingswaySoft.
  • File Columns (FileType) – Not supported by most migration tools and require API-based uploads.

At first, my automation failed repeatedly, even though I was following best practices. It took a lot of testing and API debugging to understand that Dataverse FileType columns require a very specific upload process, which standard ETL tools don’t support.

After spending time troubleshooting, I identified the right approach:

  • Query SharePoint for the correct file paths
  • Download files locally before processing
  • Read files as binary data (ensuring correct encoding)
  • Upload files to Dataverse via Web API with the right headers and structure

This wasn’t a simple fix—it required digging into API responses, testing different content types, and adjusting request formats.


The Solution: PowerShell + SharePoint + Dataverse API

After multiple iterations, I finalized a working solution that automates the process from start to finish. Here’s a simplified version of the approach (with dummy data).


# Authenticate with Dataverse
$conn = Connect-CrmOnlineDiscovery -InteractiveMode
$token = $conn.CurrentAccessToken
$SiteUrl = "https://example.crm.dynamics.com"

# Fetch Matching Files from SharePoint
$fetchXml = @"
<fetch mapping="logical">
    <entity name="sharepointdocumentlocation">
        <attribute name="relativeurl" />
        <link-entity name="custom_table"
                     from="recordid"
                     to="regardingobjectid"
                     link-type="inner"
                     alias="record">
            <attribute name="record_name" />
            <attribute name="record_number" />
            <attribute name="language" />
        </link-entity>
    </entity>
</fetch>
"@
$fetchXmlEncoded = [System.Web.HttpUtility]::UrlEncode($fetchXml)
$apiUrl = "$SiteUrl/api/data/v9.2/sharepointdocumentlocations?fetchXml=$fetchXmlEncoded"

$headers = @{
    "Authorization" = "Bearer $token"
    "Accept"        = "application/json"
    "OData-Version" = "4.0"
}

$response = Invoke-RestMethod -Uri $apiUrl -Method GET -Headers $headers
$Results = $response.value  

# Connect to SharePoint
Connect-PnPOnline -Url "https://example.sharepoint.com/sites/documents" -UseWebLogin

# Define Download Path
$DownloadPath = "C:\temp\Documents"
if (!(Test-Path $DownloadPath)) {
    New-Item -ItemType Directory -Path $DownloadPath -Force
}

foreach ($item in $Results) {
    $relativeUrl = $item.relativeurl
    $recordName = $item."record.record_name"
    $recordNumber = $item."record.record_number"
    $languageCode = $item."record.language"

    $isFrench = $languageCode -eq "fr"
    $folderPath = "/sites/documents/folder/$relativeUrl"

    if ($isFrench) {
        $file1 = "$folderPath/${recordName}_${recordNumber}_Document_FR.pdf"
        $file2 = "$folderPath/${recordName}_${recordNumber}_Supplement_FR.pdf"
    } else {
        $file1 = "$folderPath/${recordName}_${recordNumber}_Document_EN.pdf"
        $file2 = "$folderPath/${recordName}_${recordNumber}_Supplement_EN.pdf"
    }

    # Download Files
    $localFile1 = "$DownloadPath\${recordName}_${recordNumber}_Document.pdf"
    $localFile2 = "$DownloadPath\${recordName}_${recordNumber}_Supplement.pdf"

    try {
        Get-PnPFile -Url $file1 -Path $DownloadPath -AsFile -Force
    } catch { Write-Host "File not found: $file1" }

    try {
        Get-PnPFile -Url $file2 -Path $DownloadPath -AsFile -Force
    } catch { Write-Host "File not found: $file2" }

    # Upload to Dataverse
    if (Test-Path $localFile1) {
        $fileBytes1 = [System.IO.File]::ReadAllBytes($localFile1)
        $FileUrl1 = "$SiteUrl/api/data/v9.2/custom_table($recordNumber)/document_file/$value"

        $uploadHeaders = @{
            "Authorization" = "Bearer $token"
            "Content-Type"  = "application/octet-stream"
            "x-ms-file-name" = "${recordName}_${recordNumber}_Document.pdf"
        }

        try {
            Invoke-RestMethod -Uri $FileUrl1 -Method PUT -Headers $uploadHeaders -Body $fileBytes1
            Write-Host "Uploaded Document File"
        } catch {
            Write-Host "Error uploading Document File: $_"
        }
    }

    if (Test-Path $localFile2) {
        $fileBytes2 = [System.IO.File]::ReadAllBytes($localFile2)
        $FileUrl2 = "$SiteUrl/api/data/v9.2/custom_table($recordNumber)/supplement_file/$value"

        $uploadHeaders = @{
            "Authorization" = "Bearer $token"
            "Content-Type"  = "application/octet-stream"
            "x-ms-file-name" = "${recordName}_${recordNumber}_Supplement.pdf"
        }

        try {
            Invoke-RestMethod -Uri $FileUrl2 -Method PUT -Headers $uploadHeaders -Body $fileBytes2
            Write-Host "Uploaded Supplement File"
        } catch {
            Write-Host "Error uploading Supplement File: $_"
        }
    }
}

The Impact

This automation completely eliminated manual work, ensuring that files are automatically pulled from SharePoint and stored correctly in Dataverse. It also resolved major API-related issues that were blocking the standard migration process.

This experience also reinforced that Dataverse FileType columns require specialized handling—something that often misunderstood.

Comments

Post a Comment