Working with the Salesforce Content model can be challenging. SQL Sales offers multiple ways to work with the model, based on real world experiences of moving note data and files around.
ContentNote
From a file
The standard way to load a ContactNote record is to associate the load payload insert record with a file, residing in a UNC path, accessible to SQL Sales.
By default, from installation, SQL Sales assumes you will be loading via the standard api approach of passing in a location of a file. Ensure the configuration of your given Environment is as below:
Note, additionally ensure you have enabled the Salesforce CRM Content setting (see below)
Basic text example
In this example a basic .txt file, as seen below in notepad and in the path shown, will be loaded to Salesforce as a ContentNote.
Prepare the payload
drop table if exists ContentNote_Test1_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,convert(nvarchar(255),'Test1 Title') as Title
,convert(nvarchar(max),'C:\SQLSales\files\Test1 basic text.txt') as Content
into ContentNote_Test1_Insert
Running the example
exec ss_Loader 'insert','demo','ContentNote_Test1_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
16:19:42: Using Env|Schema: demo|dbo
16:19:42: Starting Loader for ContentNote batchsize 200
16:19:42: SSId added to ContentNote_Test1_Insert
16:19:46: Connection method BULK & SOAP API
16:19:46: Columns checked against Salesforce metadata
16:19:48: Load complete: Success:1 Failure:0
-----------------------------------------------------------------
Examine the output
You are recommended to return the test example, to familiarise yourself with the Salesforce Content data model as we work through these examples
Modify the below configuration setting depending on whether you want to return fields such as ContentNote.Content and ContentVersion.VersionData. For large volumes records, it is not always necessary to hold the binary data in the base64 fields, hence a more efficient approach to managing the replication of objects like this, is to bypass by unchecking this setting.
exec ss_Replica 'demo', 'ContentNote'
exec ss_Replica 'demo', 'ContentVersion'
exec ss_Replica 'demo', 'ContentDocument'
By inserting this one ContentNote record, Salesforce has created the following:
ContentNote holds the Note, converted to a file in Salesforce, the binary data of which is held in ContentNote.Content.
ContentNote.Id is in fact the ContentDocument.Id
ContentNote.LatestPublishedVersionId is the ContentVersion.Id
ContentVersion.FirstPublishLocationId is by default the Load User typically or ContentNote.OwnerId if you had specified one
ContentNote.Content is the same as ContentVersion.VersionData
Shown in Salesforce:
HTML text example
In this example an html txt file, as seen below in notepad and opening in a browser, will be submitted. Salesforce will happily load an html formatted document to a ContentNote record. The path for this example is also shown below.
Prepare the payload
drop table if exists ContentNote_Test2_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,convert(nvarchar(255),'Test2 Title') as Title
,convert(nvarchar(max),'C:\SQLSales\files\Test2.html') as Content
into ContentNote_Test2_Insert
Running the example
exec ss_Loader 'insert','demo','ContentNote_Test2_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
17:34:58: Using Env|Schema: demo|dbo
17:34:58: Starting Loader for ContentNote batchsize 200
17:34:58: SSId added to ContentNote_Test2_Insert
17:35:01: Connection method BULK & SOAP API
17:35:01: Columns checked against Salesforce metadata
17:35:04: Load complete: Success:1 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
From data, basic text
The alternative method which SQL Sales has made possible, is to load SQL Server data to ContentNote, without the need for a file of the Note to be referenced as with the previous approach.
You will have to ensure the configuration of your given Environment is as below (as the data will be loaded as data and not from a file):
Basic text example
In this example, the hard-coded text “Test, direct text approach” is representing a varchar / nvarchar text field you may have from a data source you have staging in SQL Server. Note that the preparation above converts to data type varbinary(max).
drop table if exists ContentNote_Test3_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,convert(nvarchar(255),'Test3 Title') as Title
,convert(varbinary(max),'Test, direct text approach') as ContentText
,convert(varchar(max),null) as Content
into ContentNote_Test3_Insert
The next step is to convert, using the above code, the varbinary(max) to varchar(max). Use these code snippets as a foundation for your own TSQL coding, likely with many more records.
update ContentNote_Test3_Insert
set Content = convert(varchar(max),CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("ContentText")))', 'varchar(max)'))
Running the example
exec ss_Loader 'insert','demo','ContentNote_Test3_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
18:46:22: Using Env|Schema: demo|dbo
18:46:22: Starting Loader for ContentNote batchsize 200
18:46:22: SSId added to ContentNote_Test3_Insert
18:46:27: Connection method BULK & SOAP API
18:46:27: Columns checked against Salesforce metadata
18:46:30: Excluded: ContentText is not available on object ContentNote
18:46:30: Load complete: Success:1 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
From data, existing base64
As with the previous basic example, you will have to ensure the configuration of your given Environment is as below (as the data will be loaded as data and not from a file):
In this example, we will be taking the existing base64 binary data directly from SQL Server, in this case from the ContentNote.Content varbinary(max) data from the previously loaded examples.
Existing base64 example
drop table if exists ContentNote_Test4_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,convert(nvarchar(255),'Test4 Title (from ' + Title + ')') as Title,convert(varchar(max),CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("Content")))', 'varchar(max)')) as Content
into ContentNote_Test4_Insert
from ContentNote
Running the example
exec ss_Loader 'insert','demo','ContentNote_Test4_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
19:07:06: Using Env|Schema: demo|dbo
19:07:06: Starting Loader for ContentNote batchsize 200
19:07:06: SSId added to ContentNote_Test4_Insert
19:07:08: Connection method BULK & SOAP API
19:07:08: Columns checked against Salesforce metadata
19:07:12: Load complete: Success:3 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
ContentVersion
From a file
The standard way to load a ContentDocument (ContentVersion) record is to associate the load payload insert record with a file, residing in a UNC path, accessible to SQL Sales and therefore also accessible to the SQL Server service account which is running your MSSQL.
By default, from installation, SQL Sales assumes you will be loading via the standard api approach of passing in a location of a file. Ensure the configuration of your given Environment is as below:
Any file can be loaded to Salesforce, in the following example, four files of different types will be loaded in the same payload.
Prepare the payload
Your ContentVersion payload table must contain these fields
Field | Datatype | Purpose |
Id | nchar(18) | null on the insert payload |
Error | nvarchar(255) | null on the insert payload |
Title | nvarchar(255) | Title of the loaded document |
Origin | char(1) | C = (standard option) for Content H = Salesforce files from the user’s My Files (Chatter but also for files external to Salesforce) |
ContentLocation | char(1) | S = Document is in Salesforce E = Document is outside of Salesforce L = Document is on a Social Network |
Description | nvarchar(255) | Description of the loaded document |
VersionData | nvarchar(500) | Full path to the file, including the filename |
PathOnClient | nvarchar(255) | The filename |
ContentUrl | nvarchar(255) | Not Required for this example |
FirstPublishLocationId | nchar(18) | If left blank this will automatically be set as the load User's User.Id, otherwise you can specify a User or Object (i.e. the "Parent" record, for example Account). Alternatively, you can specify a Library (a ContentWorkspace). |
In this example, we will set the FirstPublishLocationId as an Account record, this will automatically set the “Parent” as this Account record, i.e. a ContentDocumentLink record will be automatically created with the LinkedEntityId set as the Account.Id.
Additionally in this example, we could set the ContentVersion.OwnerId as a User other than the load user. This will automatically create a second ContentDocumentLink record where the LinkedEntityId is set as the User.Id used in the ContentVersion.OwnerId.
If no ContentVersion.OwnerId is set, the default will be set as the load user (i.e. the username defined in the given Environment). If the load user is not set as the ContentVersion.OwnerId then it will be more challenging retrieving (replicating) the ContentVersion records just inserted, as the load user will not have sufficient permissions (as no entry in ContentDocumentLink will exist).
Example
drop table if exists ContentVersion_Insert
create table ContentVersion_Insert
(Id nchar(18)
,Error nvarchar(255)
,Title nvarchar(255)
,ContentDocumentId nchar(18)
,Origin char(1)
,ContentLocation char(1)
,OwnerId nchar(18)
,Description nvarchar(255)
,VersionData nvarchar(500)
,PathOnClient nvarchar(255)
,FirstPublishLocationId nchar(18))
insert ContentVersion_Insert
(Title
,Origin
,ContentLocation
,Description
,VersionData
,PathOnClient
,FirstPublishLocationId)
select
'pdf example' --Title
,'C' --Origin
,'S' --ContentLocation
,'Example of loading a pdf from a file' --Description
,'C:\SQLSales\files\File Test1.pdf' --VersionData
,'File Test1.pdf' --PathOnClient
,'0018d00000cjHAvAAM' --FirstPublishLocationId
union select
'docx example' --Title
,'C' --Origin
,'S' --ContentLocation
,'Example of loading a docx from a file' --Description
,'C:\SQLSales\files\File Test2.docx' --VersionData
,'File Test2.docx' --PathOnClient
,'0018d00000cjHAvAAM' --FirstPublishLocationId
union select
'xlsx example' --Title
,'C' --Origin
,'S' --ContentLocation
,'Example of loading a xlsx from a file' --Description
,'C:\SQLSales\files\File Test3.xlsx' --VersionData
,'File Test3.xlsx' --PathOnClient
,'0018d00000cjHAvAAM' --FirstPublishLocationId
union select
'jpg example' --Title
,'C' --Origin
,'S' --ContentLocation
,'Example of loading a jpg from a file' --Description
,'C:\SQLSales\files\File Test4.jpg' --VersionData
,'File Test4.jpg' --PathOnClient
,'0018d00000cjHAvAAM' --FirstPublishLocationId
Running the example
exec ss_Loader 'insert','demo','ContentVersion_Test1_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
21:19:39: Using Env|Schema: demo|dbo
21:19:39: Starting Loader for ContentVersion batchsize 200
21:19:39: SSId added to ContentVersion_Test1_Insert
21:19:42: Connection method BULK & SOAP API
21:19:42: Columns checked against Salesforce metadata
21:19:47: Load complete: Success:4 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
External link only, not held in Salesforce
An alternative approach is to load only links to Content held outside of Salesforce.
As with the previous standard approach, by default, from installation, SQL Sales assumes you will be loading via the standard api approach of passing in a location of a file. Ensure the configuration of your given Environment is as below:
In the example to follow, there is no authentication required to access the external file however an ExternalDataSource will still need to be defined in our development Org. In practise you may need to setup more involved security for your use case, which is covered by Salesforce documentation. "HiDrive" is just a third party host provider that SQL-Sales uses but this could be any Content host external to Salesforce.
The following screen shows an ExternalDataSource being added in Salesforce setup.
This precursor step is necessary as the resultant ExternalDataSource.Id is required in the ContentVersion payload to follow. Once you have added in Salesforce, replicate back to SQL with:
exec ss_Replica 'demo', 'ExternalDataSource'
Your ContentVersion payload table must contain these fields
Field | Datatype | Purpose |
Id | nchar(18) | null on the insert payload |
Error | nvarchar(255) | null on the insert payload |
Title | nvarchar(255) | Title of the loaded document |
Origin | char(1) | H = Chatter, but also for outside of Salesforce |
ContentLocation | char(1) | E = Document is outside of Salesforce |
Description | nvarchar(255) | Description of the loaded document |
ContentUrl | nvarchar(255) | Full url to the Content |
ExternalDocumentInfo1 | nvarchar(255) | Full url to the Content |
ExternalDataSourceId | nchar(18) | (ExternalDataSource.Id from the earlier setup in Salesforce) |
FirstPublishLocationId | nchar(18) | If left blank this will automatically be set as the load User's User.Id, otherwise you can specify a User or Object (i.e. the "Parent" record, for example Account). Alternatively, you can specify a Library (a ContentWorkspace). |
In this example, we will set the FirstPublishLocationId as an Account record, this will automatically set the “Parent” as this Account record, i.e. a ContentDocumentLink record will be automatically created with the LinkedEntityId set as the Account.Id.
Additionally in this example, we could set the ContentVersion.OwnerId as a User other than the load user. This will automatically create a second ContentDocumentLink record where the LinkedEntityId is set as the User.Id used in the ContentVersion.OwnerId.
If no ContentVersion.OwnerId is set, the default will be set as the load user (i.e. the username defined in the given Environment). If the load user is not set as the ContentVersion.OwnerId then it will be more challenging retrieving (replicating) the ContentVersion records just inserted, as the load user will not have sufficient permissions (as no entry in ContentDocumentLink will exist).
Example
drop table if exists ContentVersion_ExternalTest_Insert
create table ContentVersion_ExternalTest_Insert
(Id nchar(18)
,Error nvarchar(255)
,Title nvarchar(255)
,ContentDocumentId nchar(18)
,Origin char(1)
,ContentLocation char(1)
,OwnerId nchar(18)
,Description nvarchar(255)
,ContentUrl nvarchar(255)
,ExternalDocumentInfo1 nvarchar(255)
,ExternalDataSourceId nvarchar(255)
,FirstPublishLocationId nchar(18))
insert ContentVersion_ExternalTest_Insert
(Title
,Origin
,ContentLocation
,Description
,ContentUrl
,ExternalDocumentInfo1
,ExternalDataSourceId
,FirstPublishLocationId)
select'image example remote' --Title
,'H' --Origin
,'E' --ContentLocation
,'Example of linking to external Content' --Description
,'https://hidrive.ionos.com/lnk/JQGN0ptz' as ContentUrl
,'https://hidrive.ionos.com/lnk/JQGN0ptz' as ExternalDocumentInfo1
,'0XC8d0000000Gi0GAE' as ExternalDataSourceId
,'0018d00000P4XctAAF' FirstPublishLocationId
Running the example
exec ss_Loader 'insert','demo','ContentVersion_ExternalTest_Insert'
SQL-SALES insert run date: 2024-01-12 ---------------------------
23:20:07: Using Env|Schema: demo|dbo
23:20:07: Starting Loader for ContentVersion batchsize 200
23:20:07: SSId added to ContentVersion_ExternalTest_Insert
23:20:10: Connection method BULK & SOAP API
23:20:10: Columns checked against Salesforce metadata
23:20:10: Starting load for ContentVersion_ExternalTest_Insert
23:20:13: Load complete: Success:1 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
Working with ContentDocumentLink
Earlier, it was mentioned there will be permissions issues accessing ContentVersion data if the load user is not present in ContentDocumentLink for a given ContentDocument that is required (when pulling via ss_Replica or ss_Delta).
It is recommended to read up on the ContentDocumentLink section in this guide. As a shortcut, here we have passed in the AccountId used in this example. This returns the 4 ContentDocumentIds for the four loaded files:
exec ss_Replica 'demo', 'ContentDocumentLink','LinkedEntityId','0018d00000cjHAvAAM'
select * from ContentDocumentLink
The 4 DocumentIds can be passed in here:
exec ss_Replica 'demo', 'ContentDocumentLink','ContentDocumentId','0698d00000PZ4zYAAT,0698d00000PZ4zZAAT,0698d00000PZ4zaAAD,0698d00000PZ4zbAAD'
select * from ContentDocumentLink
Here we see all linked entities for the ContentDocumentIds passed in.
From data
The alternative method which SQL Sales has made possible, is to load SQL Server data to ContentVersion, without the need for a file of the document to be referenced as with the previous approach.
You will have to ensure the configuration of your given Environment is as below (as the data will be loaded as data and not from a file):
In this example, we will be taking the existing base64 binary data directly from SQL Server, in this case from the ContentVersion.VersionData varbinary(max) data from the previously loaded examples.
Existing base64 example
drop table if exists ContentVersion_Test2_Insert
select
convert(nchar(18),null) as Id
,convert(nvarchar(255),null) as Error
,convert(nvarchar(255)
,'File Test2 Title (from ' + Title + ')') as Title
,PathOnClient
,convert(varchar(max),CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("VersionData")))', 'varchar(max)')) as VersionData
into ContentVersion_Test2_Insert
from ContentVersion
where Description like 'Example of loading a % from a file'
Note, PathOnClient is mandatory for VersionData, hence included in this example
Running the example
exec ss_Loader 'insert','demo','ContentVersion_Test2_Insert'
SQL-SALES insert run date: 2023-11-11 ---------------------------
22:04:15: Using Env|Schema: demo|dbo
22:04:15: Starting Loader for ContentVersion batchsize 200
22:04:15: SSId added to ContentVersion_Test2_Insert
22:04:18: Connection method BULK & SOAP API
22:04:18: Columns checked against Salesforce metadata
22:04:22: Load complete: Success:4 Failure:0
-----------------------------------------------------------------
Shown in Salesforce:
