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 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 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 = 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: OKActual Issue = not Createable/Updateable PermittedNot Applicable = control columns (Id, Error) Ignored for Actionn/a = Not Applicable
DataTypeStatus	Structural compatibility between SQL type and SF type Values: OKPotential Issue = e.g. precision/length mismatch Actual Issue = incompatible types PermittedIgnored for Action Does NOT validate actual values
ContentStatus	Result of actual data checks (optional) Values: Checked (or OK depending on your naming) Not CheckedActual IssuePermittedIgnored for Action Only reflects checks implemented in this procedure, not full SF validation
Detail	Human-readable explanation of findings Format: 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 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 fieldsExample: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.