Salesforce Load (ss_Loader) Schema Check #

Purpose #

ss_SchemaCheck validates a SQL load table for use with ss_Loader against Salesforce field and object metadata before executing a load.

The output is a column-level report highlighting:

• mapping issues
• permission constraints
• datatype risks
• optional data content issues

Parameters #

Parameter	Purpose
@Env	SQL Sales Environment Name, see Setup
@TableName	Source load table (e.g. Account_demo_Insert)
@Operation	Defines validation behaviour based on Salesforce API operation eg “Insert”
@PermittedSuffixes	Columns with these suffixes are intentionally ignoredDeployed with:@PermittedSuffixes nvarchar(4000) = N’_Orig,_Info’Example:Name_Orig Amount_Info
@PermittedColumnNames	Explicitly ignored columns (e.g. system/helper fields)Deployed with:@PermittedColumnNames nvarchar(4000) = N’SSId’Example:SSId
@CheckDataContents	Controls row-level validation:Deployed with:@CheckDataContents bit = 1
@CheckReferencePrefix	Validates Salesforce ID prefixesValidates the first 3 and enforces case-sensitive comparison for Salesforce ID keyprefix validation to ensure accurate detection of mismatched references.Deployed with:@CheckReferencePrefix bit = 0
@UpsertRequireInsertFields	Controls UPSERT strictness:Deployed with:@UpsertRequireInsertFields bit = 0
@Special1	Future use, not currently used
@Special2	Future use, not currently used

Usage Example #

exec dbo.ss_SchemaCheck
@Env = 'UAT'
,@TableName = 'Account_demo_Insert'
,@Operation = 'Insert'
,@PermittedSuffixes = '_Orig,_Info'
,@PermittedColumnNames = 'SSId'
,@CheckDataContents = 1
,@CheckReferencePrefix = 0
,@UpsertRequireInsertFields  = 0

Key parameters in detail #

@Env #

Your pre-configured Environment (see Configuration Manager)

@TableName #

Source load table (e.g. Account_demo_Insert)

@Operation #

Defines validation behaviour based on Salesforce API operation.

Supported values:

Insert operations #

• Insert
• BulkAPIv1Insert
• BulkAPIv2Insert

Validates:

• Createable fields
• Required fields present

Update operations #

• Update
• BulkAPIv1Update
• BulkAPIv2Update

Validates:

• Updateable fields only
• Required fields NOT enforced

Upsert operations #

• Upsert
• BulkAPIv1Upsert
• BulkAPIv2Upsert

Hybrid behaviour:

Controlled by:

• @UpsertRequireInsertFields

Setting	Behaviour
1	Validate like INSERT (strict)
0	Validate like UPDATE (relaxed)

Delete operations #

• Delete
• BulkAPIv1Delete
• BulkAPIv2Delete

Undelete #

• Undelete

Hard delete #

• BulkAPIv1Harddelete
• BulkAPIv2Harddelete

Delete / Undelete / HardDelete behaviour #

For all delete-type operations:

Only these columns are required:

• Id
• Error

All other columns are:

• ignored
• marked as Ignored for Action

@PermittedSuffixes #

Columns with these suffixes are intentionally ignored

Example:

Name_Orig Amount_Info

@PermittedColumnNames #

Explicitly ignored columns (e.g. system/helper fields)

Example:

SSId

@CheckDataContents #

Controls row-level validation:

Value	Behaviour
1	Run data checks (length, numeric, IDs, etc.)
0	Skip content validation

@CheckReferencePrefix #

Validates Salesforce ID prefixes

Validates the first 3 and enforces case-sensitive comparison for Salesforce ID keyprefix validation to ensure accurate detection of mismatched references.

@UpsertRequireInsertFields #

Controls UPSERT strictness:

Value	Behaviour
1	Treat UPSERT like INSERT (safer option)
0	Treat UPSERT like UPDATE (looser option)

How to read the output #

Each row = one source column

OverallStatus (main result) #

Status	Meaning
OK	Safe
Permitted	Ignored by design
Potential Issue	Risk
Actual Issue	Problem
Ignored for Action	Not relevant
n/a	Not Applicable / Not relevant

Validation layers #

ExistenceStatus #

Does the column exist in Salesforce?

PermissionStatus #

Can the field be written to?

DataTypeStatus #

Is the SQL datatype structurally compatible?

Does NOT validate actual values

ContentStatus #

Result of actual data checks

Status	Meaning
Checked	Passed implemented checks
Not Checked	Skipped
Actual Issue	Invalid data found
Permitted	Ignored
Ignored for Action	Not relevant
n/a	Not Applicable / Not relevant

Detail column #

Explains exactly what was detected and is a useful debugging aid

[Datatype] SQL text type is acceptable base type
[Datatype] Text column length is not 18 characters
[Content] Invalid Salesforce Id detected
[Existence] Column suffix _Info is permitted
[Synthetic] Required field missing

Mandatory load columns #

Id – must be 18-char text

Error – must be nvarchar(255)

Permitted columns #

Ignored safely:

suffix-based (_Orig, _Info)

explicit names (SSId)

Synthetic rows #

Example:

Missing Required Target Field

Salesforce requires this field

Source table does not include it

What this tool does NOT validate #

The procedure does NOT validate:

• picklist values
• reference record existence
• Salesforce validation rules
• triggers / flows
• business logic

ContentStatus = Checked means:

“No issues found in the checks implemented here” but this is not a guaranteed load success.

Workflow #

1. Run ss_SchemaCheck
2. Filter OverallStatus <> OK
3. Fix:
   • Actual Issues first
   • then review Potential Issues
4. Ignore Permitted / Ignored for Action
5. Proceed with load

Note #

• Use @CheckDataContents = 1 for full validation
• Use strict UPSERT mode during testing
• Use suffixes for helper columns
• Always read the Detail column

Summary #

This procedure is a:

Pre-load validation tool for Salesforce data loads

It helps you:

• detect schema mismatches
• reduce load failures
• understand data risks
• safely manage helper/system columns

When debugging #

Check in order:

1. OverallStatus
2. Detail
3. DataTypeStatus
4. ContentStatus
5. BadRowCount

Output Report Definition #

You provided table name will be reported as a table with the same name and “_Report” suffixed. This is outputted to the screen on execution and preserved in this created table.

Column	Purpose
OverallStatus	Final consolidated result for the column ​Values:OK = safePermitted = intentionally ignoredPotential Issue = riskActual Issue = problemIgnored for Action = not relevant (e.g. delete)n/a = Not ApplicableLogic: Derived from:ExistenceStatusPermissionStatusDataTypeStatusContentStatusThis is the primary column for filtering and decision-making
ExistenceStatus	Does the column map to Salesforce?Values:OK = exists in SF metadataPermitted = ignored via suffix or explicit ruleActual Issue = not found in SFIgnored for Action = delete-type loadsn/a = Not Applicable
PermissionStatus	Can the field be written to in Salesforce?Values:OKActual Issue = not Createable/UpdateablePermittedNot Applicable = control columns (Id, Error)Ignored for Actionn/a = Not Applicable
DataTypeStatus	Structural compatibility between SQL type and SF typeValues:OKPotential Issue = e.g. precision/length mismatchActual Issue = incompatible typesPermittedIgnored for ActionDoes NOT validate actual values
ContentStatus	Result of actual data checks (optional)Values:Checked (or OK depending on your naming)Not CheckedActual IssuePermittedIgnored for ActionOnly reflects checks implemented in this procedure, not full SF validation
Detail	Human-readable explanation of findingsFormat:Multi-part, pipe-delimitedTagged with categories:Examples:[Datatype] SQL text type is acceptable base type [Content] Invalid Salesforce Id detected [Existence] Column suffix _Info is permitted [Synthetic] Required field missingPrimary debugging column
BadRowCount	Number of rows failing a content checkNULL if no failure or check not applicablePopulated only when issues found
SourceColumnName	Actual column name in load table
RowCategory	Classifies row typeValues:Mandatory Load Column → Id, ErrorSource Column → normal columnsMissing Required Target Field → synthetic rows
BaseColumnName	Column name after removing suffixExample:Name_Info = NameUsed for:mapping to Salesforce field
SourceDataType	SQL datatype (e.g. nvarchar, int, nchar)
SourceMaxLengthBytes	Raw SQL Server storage size
SourceMaxLengthChars	Logical character length (important for Unicode)
SourcePrecision / SourceScale	For numeric types
SourceNullable	Whether column allows NULL
SourceIdentity	Identity column flag
SourceComputed	Computed column flag
SFFieldName	API name in Salesforce
SFFieldLabel	User-friendly label
SFDataType	Salesforce datatype (e.g. string, reference, currency, boolean)
SFLength / SFByteLength	Max field size
SFPrecision / SFScale	Numeric constraints
SFCreateable / SFUpdateable	Permissions from metadata
SFUnique	Field must be unique
SFNillable	Allows NULL
SFExternalId	External ID flag
SFNameField	Is the object’s Name field
SFReferenceTo	Target object for reference fieldsExample:Account User
SuffixMatched	Matched suffix from:@PermittedSuffixes = ‘_Orig,_Info’
IsPermittedByName	Explicit override flagExample:@PermittedColumnNames = ‘SSId’
OrdinalPosition	Column order in source tableSpecial cases:very large values (e.g. 300000001) = synthetic rows

Run it. Fix issues. Load with confidence.