---
title: Schema Check
description: "# Salesforce Load (ss_Loader) Schema Check Table of Contents - [Salesforce Load (ss_Loader) Schema Check](#salesforce-load-ssloader-schema-check) - [Purpose](#purpose) - [Parameters](#parameters) - [Usage Example](#usage-example) - [Key parameters in detail](#key-parameters-in-detail) - [@Env](#env) - [@TableName](#tablename) - [@Operation](#operation) - [Insert operations](#insert-operations) - [Update operations](#update-operations) -…"
url: "https://sql-sales.com/docs/schema-check/"
updated: "2026-08-31"
category: Loading
---

# Schema Check

# Salesforce Load (ss_Loader) Schema Check

 Table of Contents

- [Salesforce Load (ss_Loader) Schema Check](#salesforce-load-ssloader-schema-check)
  - [Purpose](#purpose)
  - [Parameters](#parameters)
  - [Usage Example](#usage-example)
  - [Key parameters in detail](#key-parameters-in-detail)
  - [@Env](#env)
  - [@TableName](#tablename)
  - [@Operation](#operation)
  - [Insert operations](#insert-operations)
  - [Update operations](#update-operations)
  - [Upsert operations](#upsert-operations)
  - [Delete operations](#delete-operations)
  - [Undelete](#undelete)
  - [Hard delete](#hard-delete)
  - [Delete / Undelete / HardDelete behaviour](#delete--undelete--harddelete-behaviour)
  - [@PermittedSuffixes](#permittedsuffixes)
  - [@PermittedColumnNames](#permittedcolumnnames)
  - [@CheckDataContents](#checkdatacontents)
  - [@CheckReferencePrefix](#checkreferenceprefix)
  - [@UpsertRequireInsertFields](#upsertrequireinsertfields)
  - [How to read the output](#how-to-read-the-output)
  - [OverallStatus (main result)](#overallstatus-main-result)
  - [Validation layers](#validation-layers)
    - [ExistenceStatus](#existencestatus)
    - [PermissionStatus](#permissionstatus)
    - [DataTypeStatus](#datatypestatus)
    - [ContentStatus](#contentstatus)
  - [Detail column](#detail-column)
  - [Mandatory load columns](#mandatory-load-columns)
  - [Permitted columns](#permitted-columns)
  - [Synthetic rows](#synthetic-rows)
  - [What this tool does NOT validate](#what-this-tool-does-not-validate)
  - [Workflow](#workflow)
  - [Note](#note)
  - [Summary](#summary)
  - [When debugging](#when-debugging)
  - [Output Report Definition](#output-report-definition)

## 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.