SLAM_load_blob

Overview

Files can be loaded to ContentVersion, ContentNote, Attachment and Document in a similar fashion to loading record data. To upload files, set @api = 'blob'. Provide the path to the file in the Salesforce "binary field" and ensure all other required fields are supplied. Specifically, this means:

• ContentNote: put the path to the file in field Content
• ContentVersion: put the path to the file in field VersionData.
  • Salesforce also requires PathOnClient for its own FileType determination and the file preview in Lightning. It is not required to be a real filepath, but it must use a valid file type extension.
• Attachment: put the path to the file in field Body
• Document: put the path to the file in field Body

Example ContentVersion table:

Id	Title	Description	PathOnClient	VersionData
NULL	Test Document 1	Test Document 1 Description	could\be\any\real or imaginary\path\demo document 1.txt	C:\Speediful_SLAM_blob\demo document 1.txt
NULL	Test Document 2	Test Document 2 Description	but\the\extension\matters\demo document 2.txt	C:\Speediful_SLAM_blob\demo document 2.txt
NULL	Large Image	Large Image Description	to\salesforce\large image.bmp	C:\Speediful_SLAM_blob\large image.bmp

Example ContentNote table:

Id	Title	Content
NULL	demo document 2.txt	C:\Speediful_SLAM_blob\demo document 2.txt

Parameters

Name	Type	Default	Description
@sObject	NVARCHAR	Required	Salesforce object name (ContentVersion, ContentNote, Attachment, or Document)
@table	NVARCHAR	Required	Input SQL table containing data to load
@operation	NVARCHAR	Required	Operation type ('insert' or 'update')
@dry_run	BIT	0	When set to 1, validates field mapping and reports results without sending any data to Salesforce. See Dry Run

Inserting Files

To insert files, set @api = 'blob'. The API selection and file upload process are automatically managed to ensure files of any size (up to the 2GB limit of Salesforce) are uploaded. This means the @batch_size parameter is ignored.

CREATE TABLE ContentVersion_insert (
    Id NVARCHAR(18),
    Title NVARCHAR(255),
    Description NVARCHAR(255),
    PathOnClient NVARCHAR(255),
    VersionData NVARCHAR(255)
);

INSERT INTO ContentVersion_insert(
    Title,
    Description,
    PathOnClient,
    VersionData
)
VALUES (
    'Test Document 1',
    'Test Document 1 Description',
    '\share\demo document 1.txt',
    'C:\Speediful_SLAM_blob\demo document 1.txt'
);

EXEC dbo.SLAM_load
    @sObject = 'ContentVersion',
    @table = 'ContentVersion_insert',
    @api = 'blob',
    @operation = 'insert';

Updating Files

You can either update just the attributes of uploaded files (e.g. Description, Name or Title) or, where permitted, update the attributes and replace the file itself.

tip

To update attributes only, use @api of 'soap', 'bulk', or 'bulkv2'. The Salesforce "binary field" will be ignored.

To replace the file, use @api = 'blob'. This uploads the file along with the attributes.

Salesforce only permits Attachment and Document objects to replace the file. This type of operation is not permitted for the versioned objects ContentVersion and ContentNote - instead you must upload a new version via insert. Therefore you cannot combine @operation = 'update' with @api = 'blob' for ContentVersion or ContentNote.

Salesforce Org to Org Migrations

With Salesforce org to org migrations, the following special considerations apply:

Downloaded Filepath

You must provide the path to the downloaded file, retrieved using SLAM_download_blobs. This file is located, by default, at C:\Speediful_SLAM_blob\{salesforce_id}.file.

A useful SQL expression for this filepath is CONCAT('C:\Speediful_SLAM_blob\', Id, '.file'), which should map onto the binary field of the Salesforce target object (ContentNote.Content, ContentVersion.VersionData, Attachment.Body, Document.Body).

Original Filename and Extension

While not unique to org to org migrations, you must also migrate the original, unmodified filename and extension data, so that filename and extension information is not lost. The table below lists these fields:

sObject	Field(s) to Migrate	Example Values
ContentVersion	PathOnClient	C:\Users\jdoe\Documents\pitch-deck.pdf
Attachment	Name	pitch-deck.pdf
Document	Name Type	pitch-deck pdf

Example SQL for ContentVersion

Putting these considerations together, below is an example mapping and transformation to stage data prior to loading.

INSERT INTO ContentVersion_LOAD
SELECT
    NULL AS Id,
    PathOnClient, -- real file extension, e.g. C:\Users\jdoe\Documents\pitch-deck.pdf
    CONCAT('C:\Speediful_SLAM_blob\', Id, '.file') AS VersionData,
    Title,
    Description,
    -- <additional 1-1 mappings below ↓>
    ContentLocation,
    CreatedDate,
    ContentUrl,
    ReasonForChange,
    SharingOption,
    SharingPrivacy,
    ContentModifiedDate,
    LastModifiedDate,
    TagCsv,
    Origin,
    ExternalDocumentInfo1,
    ExternalDocumentInfo2,
    IsMajorVersion,
    IsAssetEnabled,
    <add lookup and custom fields>
FROM dbo.ContentVersion_SOURCE;

Usage Examples

Updating file attributes only:

CREATE TABLE Document_update (
    Id NVARCHAR(18),
    Name NVARCHAR(255),
    Description NVARCHAR(255)
);

INSERT INTO Document_update (Id, Name, Description)
VALUES
    ('015As000007omqDIAQ', 'Updated Name', 'Updated Description');

EXEC dbo.SLAM_load
    @sObject = 'Document',
    @table = 'Document_update',
    @api = 'bulk',
    @operation = 'update';

Updating file attributes and replacing the file:

CREATE TABLE Document_update (
    Id NVARCHAR(18),
    Name NVARCHAR(255),
    Description NVARCHAR(255),
    Body NVARCHAR(255)
);

INSERT INTO Document_update (Id, Name, Description, Body)
VALUES
    ('015As000007omqDIAQ', 'Updated Name', 'Updated Description', 'C:\Speediful_SLAM_Blob\replacement file.txt');

EXEC dbo.SLAM_load
    @sObject = 'Document',
    @table = 'Document_update',
    @api = 'blob',
    @operation = 'update';