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.

It answers:

“Will this table load cleanly into Salesforce, and if not, why?”

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 ignored Deployed 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 prefixes Validates 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

Run ss_SchemaCheck
Filter OverallStatus <> OK
Fix:
- Actual Issues first
- then review Potential Issues
Ignore Permitted / Ignored for Action
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:

OverallStatus
Detail
DataTypeStatus
ContentStatus
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` = safe `Permitted` = intentionally ignored `Potential Issue` = risk `Actual Issue` = problem `Ignored for Action` = not relevant (e.g. delete) `n/a` = Not Applicable Logic: Derived from: ExistenceStatus PermissionStatus DataTypeStatus ContentStatus This is the primary column for filtering and decision-making
ExistenceStatus	Does the column map to Salesforce? Values: `OK` = exists in SF metadata `Permitted` = ignored via suffix or explicit rule `Actual Issue` = not found in SF `Ignored for Action` = delete-type loads `n/a` = Not Applicable
PermissionStatus	Can the field be written to in Salesforce? Values: `OK` `Actual Issue` = not Createable/Updateable `Permitted` `Not Applicable` = control columns (Id, Error) `Ignored for Action` `n/a` = Not Applicable
DataTypeStatus	Structural compatibility between SQL type and SF type Values: `OK` `Potential Issue` = e.g. precision/length mismatch `Actual Issue` = incompatible types `Permitted` `Ignored for Action` Does NOT validate actual values
ContentStatus	Result of actual data checks (optional) Values: `Checked` (or `OK` depending on your naming) `Not Checked` `Actual Issue` `Permitted` `Ignored for Action` Only reflects checks implemented in this procedure, not full SF validation
Detail	Human-readable explanation of findings Format: Multi-part, pipe-delimited Tagged 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 missing Primary debugging column
BadRowCount	Number of rows failing a content check NULL if no failure or check not applicable Populated only when issues found
SourceColumnName	Actual column name in load table
RowCategory	Classifies row type Values: `Mandatory Load Column` → Id, Error `Source Column` → normal columns `Missing Required Target Field` → synthetic rows
BaseColumnName	Column name after removing suffix Example: `Name_Info` = `Name` Used 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 fields Example: Account User
SuffixMatched	Matched suffix from: @PermittedSuffixes = '_Orig,_Info'
IsPermittedByName	Explicit override flag Example: @PermittedColumnNames = 'SSId'
OrdinalPosition	Column order in source table Special cases: very large values (e.g. `300000001`) = synthetic rows

Run it. Fix issues. Load with confidence.