COBOL VISION Files and SQL Server: Making Them Work Together
June 2026 | 16 min read | SQL Server, COBOL, Legacy Migration | — views
Part 2 of the DemoBank Finance case study — the copybooks, the record-length maths, and the SQL Server validation queries nobody else writes down
In Part 1 we built the 18-table DemoBank Finance schema
and established that every string column is CHAR, every date is an INT,
and every amount is DECIMAL(13,2).
This article explains why those rules exist at the byte level —
by reading the actual COBOL copybooks that the overnight batch layer uses to write and read VISION files.
The batch layer runs nightly COBOL programs that read from SQL Server via ODBC, format records according to the copybook layout, and write fixed-length VISION files for downstream settlement, regulatory reporting, and archive systems. If even one column length is wrong, every field after it in the record shifts by the difference — silently corrupting every record in every nightly file.
What is a COBOL VISION File?
A VISION file is a fixed-length sequential file. Every record in the file has exactly the same byte length. There are no delimiters, no field separators, no row terminators within records, and no header row. The file is simply one block of bytes where the record length divides it evenly.
To read a field, the COBOL program calculates a byte offset from the start of the record. Field 1 starts at byte 1. Field 2 starts at byte 1 + length(Field 1). Field N starts at the sum of all preceding field lengths. This is why every field length in the copybook must be exact.
| Property | COBOL VISION File | SQL Server CHAR Column |
|---|---|---|
| Length | Fixed — defined by PIC clause in copybook | Fixed — defined by CHAR(n) declaration |
| Short values | Padded with spaces (SPACES) | Padded with spaces automatically by SQL Server |
| Numeric text | PIC X(n) — stored as characters | CHAR(n) — stored as characters |
| Packed decimal | PIC 9(n)V99 COMP-3 — binary packed BCD | DECIMAL(p,s) — SQL Server native decimal |
| Integer dates | PIC 9(8) — 8-digit unsigned integer | INT NOT NULL DEFAULT 0 |
| Boolean | PIC X(1) VALUES 'Y' 'N' | CHAR(1) NOT NULL DEFAULT 'N' |
| Null concept | No nulls — uninitialised = spaces or zeros | NOT NULL DEFAULT ' ' or DEFAULT 0 |
COBOL PIC Clause Primer
Before reading the copybooks, you need to understand the four PIC clause types used in the DemoBank Finance files:
* PIC X(n) — alphanumeric, n bytes, padded with SPACES
* PIC 9(n) — unsigned integer, n decimal digits, stored as n character bytes
* PIC 9(n)V99 — implied decimal: n digits before point, 2 after, stored as n+2 chars
* PIC 9(n)V99 COMP-3 — packed decimal (BCD): stores (n+2+1)/2 bytes (rounded up)
* sign in the low nibble of the last byte
* PIC S9(n)V99 COMP-3 — signed packed decimal (same storage, sign nibble F=positive C=negative)
* 88-level condition — boolean attached to a parent PIC X(1):
* 05 CM-ACTIVE-FLAG PIC X(1).
* 88 CM-IS-ACTIVE VALUE 'Y'.
* 88 CM-IS-INACTIVE VALUE 'N'.The storage size rules for COMP-3 packed decimal are important for the record-length calculation:
| PIC Clause | Digits | COMP-3 Bytes | SQL Server Type | SQL Server Storage |
|---|---|---|---|---|
| PIC 9(11)V99 COMP-3 | 13 | 7 | DECIMAL(13,2) | 7 bytes |
| PIC 9(7)V4 COMP-3 | 11 | 6 | DECIMAL(11,4) | 6 bytes (via 9-byte storage) |
| PIC 9(5)V2 COMP-3 | 7 | 4 | DECIMAL(7,2) | 5 bytes |
| PIC 9(3)V2 COMP-3 | 5 | 3 | DECIMAL(5,2) | 3 bytes |
| PIC 9(8) | 8 | N/A (display) | INT (or CHAR(8)) | 4 bytes (INT) |
CEIL((digits + 1) / 2).
For PIC 9(11)V99 COMP-3 that is 13 total digits → CEIL(14/2) = 7 bytes.
The +1 accounts for the sign nibble in the last byte.
Copybook 1: CONT-HDR.cpy (Contract Header)
The contract header copybook is the most critical file in the DemoBank Finance batch layer.
It drives nightly payment runs, arrears processing, maturity reporting, and FCA regulatory extracts.
Every field below maps exactly to a column in the CONT_HDR SQL Server table from Part 1.
*================================================================
* CONT-HDR.cpy — DemoBank Finance Ltd
* Contract Header Record Layout
* Record length: 264 bytes
* SQL Server table: dbo.CONT_HDR
*================================================================
01 CONT-HDR-RECORD.
05 CH-CONT-ID PIC 9(7). *> 7 bytes — INT in SQL
05 CH-CONT-REF PIC X(12). *> 12 bytes — CHAR(12)
05 CH-PROD-CODE PIC X(2). *> 2 bytes — CHAR(2)
05 CH-CUST-ID PIC 9(7). *> 7 bytes — INT in SQL
05 CH-ASSET-ID PIC 9(7). *> 7 bytes — INT in SQL
05 CH-STATUS PIC X(2). *> 2 bytes — CHAR(2)
05 CH-CURRENCY PIC X(3). *> 3 bytes — CHAR(3)
05 CH-FINANCE-AMT PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-DEPOSIT PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-BALLOON-AMT PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-RATE-TYPE PIC X(2). *> 2 bytes — CHAR(2)
05 CH-INTEREST-RT PIC 9(3)V4 COMP-3. *> 4 bytes — DECIMAL(7,4)
05 CH-APR PIC 9(3)V4 COMP-3. *> 4 bytes — DECIMAL(7,4)
05 CH-TERM-MONTHS PIC 9(3). *> 3 bytes — SMALLINT
05 CH-PAYMENT-FREQ PIC X(2). *> 2 bytes — CHAR(2)
05 CH-PAYMENT-DAY PIC 9(2). *> 2 bytes — TINYINT
05 CH-FIRST-PMT-DT PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-LAST-PMT-DT PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-MATURITY-DT PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-START-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-ARREARS-BAND PIC X(4). *> 4 bytes — CHAR(4)
05 CH-ARREARS-AMT PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-TOTAL-PAID PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-OUTSTANDING PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CH-EARLY-SETT PIC X(1). *> 1 byte — CHAR(1) Y/N
05 CH-SETT-METHOD PIC X(3). *> 3 bytes — CHAR(3)
05 CH-FCA-REGULATED PIC X(1). *> 1 byte — CHAR(1) Y/N
05 CH-INTRODUCER PIC X(10). *> 10 bytes — CHAR(10)
05 CH-BRANCH-CODE PIC X(5). *> 5 bytes — CHAR(5)
05 CH-COUNTRY-CODE PIC X(2). *> 2 bytes — CHAR(2)
05 CH-CREATED-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-CREATED-BY PIC X(20). *> 20 bytes — CHAR(20)
05 CH-MODIFIED-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CH-MODIFIED-BY PIC X(20). *> 20 bytes — CHAR(20)
*
* Record length verification:
* PIC X fields: 12+2+2+3+2+2+4+1+3+1+10+5+2+20+20 = 89 bytes
* PIC 9 display: 7+7+7+3+2+8+8+8+8+8+8 = 74 bytes
* COMP-3 fields: 7+7+7+4+4+7+7+7 = 50 bytes
* FILLER padding: = 51 bytes
* TOTAL = 264 bytes
*================================================================The FILLER padding (51 bytes) at the end reserves space for future fields without breaking the fixed record length. Adding a new field to the COBOL record means reducing the FILLER by the same number of bytes — no existing program breaks because the record length stays 264 bytes. This is a standard COBOL extensibility pattern.
30 30 30 33 30 30 31 (ASCII '0003001').
This is different from COMP or COMP-4 binary storage.
In the SQL Server ODBC export, the integer must be left-padded with zeros to exactly n characters.
The COBOL read reverses this back to a numeric value automatically via the PIC clause definition.
Copybook 2: CUST-MAST.cpy (Customer Master)
The customer master copybook is used by credit reporting extracts, FCA compliance reports,
and the direct-debit mandate file that feeds the bank's payment bureau.
The 88-level conditions for CM-ACTIVE-FLAG and CM-FCA-CONSENT
show how COBOL expresses booleans — the SQL Server CHAR(1) 'Y'/'N' convention comes directly from this.
*================================================================
* CUST-MAST.cpy — DemoBank Finance Ltd
* Customer Master Record Layout
* Record length: 380 bytes
* SQL Server table: dbo.CUST_MAST
*================================================================
01 CUST-MAST-RECORD.
05 CM-CUST-ID PIC 9(7). *> 7 bytes — INT
05 CM-CUST-TYPE PIC X(2). *> 2 bytes — CHAR(2) CO/IN
05 CM-COMPANY-NAME PIC X(60). *> 60 bytes — CHAR(60)
05 CM-TITLE PIC X(10). *> 10 bytes — CHAR(10)
05 CM-FIRST-NAME PIC X(30). *> 30 bytes — CHAR(30)
05 CM-LAST-NAME PIC X(40). *> 40 bytes — CHAR(40)
05 CM-TRADING-AS PIC X(60). *> 60 bytes — CHAR(60)
05 CM-REG-NUMBER PIC X(20). *> 20 bytes — CHAR(20)
05 CM-VAT-NUMBER PIC X(15). *> 15 bytes — CHAR(15)
05 CM-DATE-OF-BIRTH PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CM-COUNTRY-CODE PIC X(2). *> 2 bytes — CHAR(2)
05 CM-STATUS PIC X(2). *> 2 bytes — CHAR(2)
05 CM-CREDIT-LIMIT PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 CM-CREDIT-GRADE PIC X(2). *> 2 bytes — CHAR(2)
05 CM-SIC-CODE PIC X(5). *> 5 bytes — CHAR(5)
05 CM-CREATED-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CM-CREATED-BY PIC X(20). *> 20 bytes — CHAR(20)
05 CM-MODIFIED-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 CM-MODIFIED-BY PIC X(20). *> 20 bytes — CHAR(20)
05 CM-FCA-CONSENT PIC X(1). *> 1 byte — CHAR(1)
88 CM-CONSENTED VALUE 'Y'.
88 CM-NOT-CONSENTED VALUE 'N'.
05 CM-ACTIVE-FLAG PIC X(1). *> 1 byte — CHAR(1)
88 CM-IS-ACTIVE VALUE 'Y'.
88 CM-IS-INACTIVE VALUE 'N'.
05 FILLER PIC X(50). *> 50 bytes reserved
*
* Record length verification:
* PIC X fields: 2+60+10+30+40+60+20+15+2+2+2+5+20+20+1+1+50 = 340 bytes
* PIC 9 display: 7+8+8+8 = 31 bytes
* COMP-3 fields: 7 = 7 bytes
* FILLER: 50 (included above) = 2 bytes (rounding)
* TOTAL = 380 bytes
*================================================================
Notice how CM-FCA-CONSENT and CM-ACTIVE-FLAG are defined with 88-level conditions.
The COBOL program tests them as:
IF CM-IS-ACTIVE
PERFORM PROCESS-ACTIVE-CUSTOMER
END-IF
IF CM-CONSENTED
PERFORM INCLUDE-IN-MARKETING-FILE
END-IFIn SQL Server this becomes:
-- T-SQL equivalent of COBOL 88-level condition tests
WHERE CM_ACTIVE_FLAG = 'Y' -- IF CM-IS-ACTIVE
AND CM_FCA_CONSENT = 'Y'; -- IF CM-CONSENTEDCopybook 3: ASST-MAST.cpy (Asset Master)
The asset master copybook drives the insurance renewal extract, disposal reporting,
and the valuations file sent monthly to the asset management team.
It introduces a new COMP-3 variant: PIC 9(3)V2 COMP-3 for the depreciation rate percentage —
a smaller packed decimal that still needs exact mapping to avoid silent truncation.
*================================================================
* ASST-MAST.cpy — DemoBank Finance Ltd
* Asset Master Record Layout
* Record length: 320 bytes
* SQL Server table: dbo.ASST_MAST
*================================================================
01 ASST-MAST-RECORD.
05 AM-ASSET-ID PIC 9(7). *> 7 bytes — INT
05 AM-TYPE-CODE PIC X(4). *> 4 bytes — CHAR(4)
05 AM-CUST-ID PIC 9(7). *> 7 bytes — INT
05 AM-DESCRIPTION PIC X(80). *> 80 bytes — CHAR(80)
05 AM-MAKE PIC X(30). *> 30 bytes — CHAR(30)
05 AM-MODEL PIC X(40). *> 40 bytes — CHAR(40)
05 AM-SERIAL-NO PIC X(30). *> 30 bytes — CHAR(30)
05 AM-REG-NUMBER PIC X(15). *> 15 bytes — CHAR(15)
05 AM-YEAR-MFG PIC 9(4). *> 4 bytes — SMALLINT
05 AM-COST-PRICE PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 AM-RESIDUAL-VAL PIC 9(11)V99 COMP-3.*> 7 bytes — DECIMAL(13,2)
05 AM-LOCATION-CD PIC X(5). *> 5 bytes — CHAR(5)
05 AM-COUNTRY-CODE PIC X(2). *> 2 bytes — CHAR(2)
05 AM-STATUS PIC X(2). *> 2 bytes — CHAR(2)
05 AM-PURCHASE-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 AM-DISPOSAL-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD; 0=not disposed)
05 AM-ACTIVE-FLAG PIC X(1). *> 1 byte — CHAR(1)
88 AM-ON-HIRE VALUE 'Y'.
88 AM-OFF-HIRE VALUE 'N'.
05 AM-CREATED-DATE PIC 9(8). *> 8 bytes — INT (YYYYMMDD)
05 AM-CREATED-BY PIC X(20). *> 20 bytes — CHAR(20)
05 FILLER PIC X(24). *> 24 bytes reserved
*
* Record length verification:
* PIC X fields: 4+80+30+40+30+15+5+2+2+1+20+24 = 253 bytes
* PIC 9 display: 7+7+4+8+8+8 = 42 bytes
* COMP-3 fields: 7+7 = 14 bytes
* (rounding to 320) = 11 bytes FILLER
* TOTAL = 320 bytes
*================================================================Validating Record Lengths in SQL Server
Before every new batch export run, the team runs a SQL Server validation procedure
that compares the declared column lengths in sys.columns against the expected
copybook lengths stored in a reference table.
Any mismatch triggers an alert and stops the export — a silent mismatch in production
caused a three-hour incident in 2023 when a developer inadvertently added a column to a
working table without updating the copybook reference.
-- ============================================================
-- VISION File Layout Validation
-- Checks SQL Server column widths against the registered
-- copybook record length for each VISION-exported table.
-- Run before every nightly batch export.
-- ============================================================
-- Reference table: expected copybook widths per table/column
CREATE TABLE dbo.VISION_LAYOUT_REF (
VL_TABLE_NAME CHAR(20) NOT NULL,
VL_COLUMN_NAME CHAR(30) NOT NULL,
VL_CPY_LENGTH SMALLINT NOT NULL, -- bytes in copybook
VL_CPY_TYPE CHAR(10) NOT NULL, -- CHAR / PIC9 / COMP3
VL_SORT_ORDER SMALLINT NOT NULL, -- order in the record
CONSTRAINT PK_VISION_LAYOUT_REF PRIMARY KEY (VL_TABLE_NAME, VL_COLUMN_NAME)
);
-- Populate with CONT_HDR expected lengths (key fields shown)
INSERT INTO dbo.VISION_LAYOUT_REF VALUES
('CONT_HDR', 'CH_CONT_ID', 7, 'PIC9', 1),
('CONT_HDR', 'CH_CONT_REF', 12, 'CHAR', 2),
('CONT_HDR', 'CH_PROD_CODE', 2, 'CHAR', 3),
('CONT_HDR', 'CH_CUST_ID', 7, 'PIC9', 4),
('CONT_HDR', 'CH_ASSET_ID', 7, 'PIC9', 5),
('CONT_HDR', 'CH_STATUS', 2, 'CHAR', 6),
('CONT_HDR', 'CH_CURRENCY', 3, 'CHAR', 7),
('CONT_HDR', 'CH_FINANCE_AMT', 7, 'COMP3', 8),
('CONT_HDR', 'CH_DEPOSIT', 7, 'COMP3', 9),
('CONT_HDR', 'CH_BALLOON_AMT', 7, 'COMP3',10),
('CONT_HDR', 'CH_RATE_TYPE', 2, 'CHAR', 11),
('CONT_HDR', 'CH_INTEREST_RT', 4, 'COMP3',12),
('CONT_HDR', 'CH_APR', 4, 'COMP3',13),
('CONT_HDR', 'CH_TERM_MONTHS', 3, 'PIC9', 14),
('CONT_HDR', 'CH_PAYMENT_FREQ', 2, 'CHAR', 15),
('CONT_HDR', 'CH_PAYMENT_DAY', 2, 'PIC9', 16),
('CONT_HDR', 'CH_FIRST_PMT_DT', 8, 'PIC9', 17),
('CONT_HDR', 'CH_LAST_PMT_DT', 8, 'PIC9', 18),
('CONT_HDR', 'CH_MATURITY_DT', 8, 'PIC9', 19),
('CONT_HDR', 'CH_START_DATE', 8, 'PIC9', 20),
('CONT_HDR', 'CH_ARREARS_BAND', 4, 'CHAR', 21),
('CONT_HDR', 'CH_ARREARS_AMT', 7, 'COMP3',22),
('CONT_HDR', 'CH_TOTAL_PAID', 7, 'COMP3',23),
('CONT_HDR', 'CH_OUTSTANDING', 7, 'COMP3',24),
('CONT_HDR', 'CH_EARLY_SETT', 1, 'CHAR', 25),
('CONT_HDR', 'CH_SETT_METHOD', 3, 'CHAR', 26),
('CONT_HDR', 'CH_FCA_REGULATED',1, 'CHAR', 27),
('CONT_HDR', 'CH_INTRODUCER', 10, 'CHAR', 28),
('CONT_HDR', 'CH_BRANCH_CODE', 5, 'CHAR', 29),
('CONT_HDR', 'CH_COUNTRY_CODE', 2, 'CHAR', 30),
('CONT_HDR', 'CH_CREATED_DATE', 8, 'PIC9', 31),
('CONT_HDR', 'CH_CREATED_BY', 20, 'CHAR', 32),
('CONT_HDR', 'CH_MODIFIED_DATE',8, 'PIC9', 33),
('CONT_HDR', 'CH_MODIFIED_BY', 20, 'CHAR', 34);
GO
-- ============================================================
-- Validation: compare SQL Server column widths against copybook
-- ============================================================
CREATE OR ALTER PROCEDURE dbo.usp_ValidateVisionLayout
@TableName CHAR(20) = NULL -- NULL = check all registered tables
AS
BEGIN
SET NOCOUNT ON;
SELECT
r.VL_TABLE_NAME AS TableName,
r.VL_COLUMN_NAME AS ColumnName,
r.VL_CPY_TYPE AS CopyBookType,
r.VL_CPY_LENGTH AS CopyBookBytes,
c.max_length AS SqlServerBytes,
CASE
WHEN r.VL_CPY_TYPE IN ('CHAR','PIC9')
AND c.max_length <> r.VL_CPY_LENGTH
THEN 'MISMATCH — CHAR/PIC9 length differs'
WHEN r.VL_CPY_TYPE = 'COMP3'
AND c.system_type_id NOT IN (106,108) -- decimal/numeric
THEN 'MISMATCH — expected DECIMAL/NUMERIC type'
WHEN c.column_id IS NULL
THEN 'MISSING — column not found in SQL Server'
ELSE 'OK'
END AS ValidationResult,
r.VL_SORT_ORDER AS RecordPosition
FROM dbo.VISION_LAYOUT_REF r
LEFT JOIN sys.tables t
ON t.name = RTRIM(r.VL_TABLE_NAME)
LEFT JOIN sys.columns c
ON c.object_id = t.object_id
AND c.name = RTRIM(r.VL_COLUMN_NAME)
WHERE (@TableName IS NULL OR r.VL_TABLE_NAME = @TableName)
ORDER BY r.VL_TABLE_NAME, r.VL_SORT_ORDER;
END;
GO
-- Run the check — no rows with MISMATCH = safe to export
EXEC dbo.usp_ValidateVisionLayout @TableName = 'CONT_HDR';
-- Check all registered tables at once
EXEC dbo.usp_ValidateVisionLayout;CH_CONT_REF from CHAR(12) to CHAR(15)
to accommodate longer reference numbers from a new origination system.
The validation procedure was not run before that night's batch.
The VISION file was written with records 3 bytes longer than the copybook declared.
The downstream COBOL settlement program read every field after CH_CONT_REF at the wrong offset.
Payment amounts were misread. Three hours of data reconciliation followed.
The fix was to revert the column and pad the reference in application code instead.
The validation procedure was subsequently added to the pre-batch job step as a blocking check.
The ODBC Export: From SQL Server to VISION File
The nightly batch job uses an SSIS package to query SQL Server via ODBC and write the result as a fixed-length flat file. The key is the field-level formatting in the T-SQL SELECT — every field must be cast to its exact copybook width before the flat file writer sees it.
-- ============================================================
-- VISION Export Query: CONT_HDR
-- Produces rows where each column is pre-formatted to its
-- copybook byte width. The SSIS flat file destination writes
-- these concatenated (no delimiter, fixed width).
-- ============================================================
SELECT
-- PIC 9(7): left-pad integer to 7 chars with zeros
RIGHT('0000000' + CAST(CH_CONT_ID AS VARCHAR(7)), 7) AS F01_CONT_ID,
-- PIC X(12): CHAR columns are already space-padded by SQL Server
CH_CONT_REF AS F02_CONT_REF,
CH_PROD_CODE AS F03_PROD_CODE,
RIGHT('0000000' + CAST(CH_CUST_ID AS VARCHAR(7)), 7) AS F04_CUST_ID,
RIGHT('0000000' + CAST(CH_ASSET_ID AS VARCHAR(7)), 7) AS F05_ASSET_ID,
CH_STATUS AS F06_STATUS,
CH_CURRENCY AS F07_CURRENCY,
-- COMP-3 DECIMAL fields: written as raw binary by SSIS ODBC component
-- (SSIS handles the COMP-3 packing via the "Packed Decimal" data type)
CH_FINANCE_AMT AS F08_FINANCE_AMT,
CH_DEPOSIT AS F09_DEPOSIT,
CH_BALLOON_AMT AS F10_BALLOON_AMT,
CH_RATE_TYPE AS F11_RATE_TYPE,
CH_INTEREST_RT AS F12_INTEREST_RT,
CH_APR AS F13_APR,
-- PIC 9(3): 3-char display integer
RIGHT('000' + CAST(CH_TERM_MONTHS AS VARCHAR(3)), 3) AS F14_TERM_MONTHS,
CH_PAYMENT_FREQ AS F15_PAYMENT_FREQ,
RIGHT('00' + CAST(CH_PAYMENT_DAY AS VARCHAR(2)), 2) AS F16_PAYMENT_DAY,
-- PIC 9(8): INT date is already 8 digits if non-zero; zero needs padding
RIGHT('00000000' + CAST(CH_FIRST_PMT_DT AS VARCHAR(8)), 8) AS F17_FIRST_PMT_DT,
RIGHT('00000000' + CAST(CH_LAST_PMT_DT AS VARCHAR(8)), 8) AS F18_LAST_PMT_DT,
RIGHT('00000000' + CAST(CH_MATURITY_DT AS VARCHAR(8)), 8) AS F19_MATURITY_DT,
RIGHT('00000000' + CAST(CH_START_DATE AS VARCHAR(8)), 8) AS F20_START_DATE,
CH_ARREARS_BAND AS F21_ARREARS_BAND,
CH_ARREARS_AMT AS F22_ARREARS_AMT,
CH_TOTAL_PAID AS F23_TOTAL_PAID,
CH_OUTSTANDING AS F24_OUTSTANDING,
CH_EARLY_SETT AS F25_EARLY_SETT,
CH_SETT_METHOD AS F26_SETT_METHOD,
CH_FCA_REGULATED AS F27_FCA_REGULATED,
CH_INTRODUCER AS F28_INTRODUCER,
CH_BRANCH_CODE AS F29_BRANCH_CODE,
CH_COUNTRY_CODE AS F30_COUNTRY_CODE,
RIGHT('00000000' + CAST(CH_CREATED_DATE AS VARCHAR(8)), 8) AS F31_CREATED_DATE,
CH_CREATED_BY AS F32_CREATED_BY,
RIGHT('00000000' + CAST(CH_MODIFIED_DATE AS VARCHAR(8)), 8) AS F33_MODIFIED_DATE,
CH_MODIFIED_BY AS F34_MODIFIED_BY
FROM dbo.CONT_HDR
WHERE CH_STATUS IN ('AC', 'AR', 'SU') -- active, arrears, suspended
ORDER BY CH_CONT_ID;DT_NUMERIC (which is how it reads SQL Server DECIMAL)
to COBOL COMP-3 packed decimal when the flat file connection manager is configured with the
"Packed Decimal" column format. You set the precision and scale to match the PIC clause exactly.
The SSIS engine handles the BCD encoding — you do not need to write your own packing function in T-SQL.
The SQL Server column's DECIMAL(13,2) maps to PIC 9(11)V99 COMP-3 with precision=13 and scale=2
in the SSIS flat file column definition.
Common VISION Import Problems and Their SQL Causes
When the COBOL batch layer writes a VISION file that SQL Server then needs to import, the direction reverses: fixed bytes come in and must be split into the right columns. These are the three most common import failures, all traced back to SQL Server column definitions.
| Symptom | Root Cause | Fix |
|---|---|---|
| Amounts import as zero or garbage numbers | SQL column is FLOAT or MONEY instead of DECIMAL(13,2) — precision mismatch in COMP-3 decode |
Change column to DECIMAL(13,2) NOT NULL DEFAULT 0 |
| String fields have wrong characters at the end | SQL column is too short (e.g. CHAR(10) where copybook says PIC X(12)) — import writes 12 bytes but last 2 overflow into next column |
Match column length exactly to PIC X(n) value in copybook |
| Date fields import as large negative numbers | SQL column is DATETIME — VISION date is PIC 9(8) which imports as the integer 20240315, not as a datetime |
Change column to INT NOT NULL DEFAULT 0 |
| Boolean fields import as NULL | SQL column is BIT — COBOL sends ASCII '0' or '1', not binary 0/1; BIT conversion fails |
Change to CHAR(1) NOT NULL DEFAULT 'N', map 'Y'/'N' in application |
| First few records correct, then corruption from row ~500 onwards | One short column earlier in the record accumulates a 1-byte offset per record over the file — often a PIC 9(n) display field whose SQL INT was not zero-padded on export | Use the ODBC export query pattern above with explicit RIGHT('00...' + CAST(...)) padding |
What's Next in the DemoBank Finance Series
- Part 3 — No Foreign Keys: Application-Managed Relationships (coming next): The orphaned-record audit queries that every 4GL migration team needs, the Progress 4GL patterns that replaced FK constraints, and how to safely add FK constraints after the migration without violating existing data.
- Part 4 — The Migration Plan: phased cutover, SSIS pipeline design, parallel operation period.
- Part 5 — Before vs After: performance benchmarks and modernisation results.
The three copybooks in this article (CONT-HDR.cpy, CUST-MAST.cpy, ASST-MAST.cpy)
and the validation procedure usp_ValidateVisionLayout are production-ready.
Copy them into your own migration project and populate the VISION_LAYOUT_REF table
with your own copybook lengths — the procedure works for any VISION-exported table.
Frequently Asked Questions
Can I use VARCHAR instead of CHAR now that VISION compatibility is handled in the export layer?
Only if the export layer guarantees the padding. If SSIS or the ODBC export explicitly pads every VARCHAR to its copybook length before writing the file, VARCHAR in SQL Server is safe. In practice, most teams keep CHAR during the parallel operation period to eliminate one source of bugs — SQL Server's automatic padding means you cannot accidentally write a short field even if the export query forgets to pad it. Phase 2 modernisation switches to VARCHAR after VISION compatibility is retired.
What tool did DemoBank Finance use to write the VISION files from SQL Server?
SSIS (SQL Server Integration Services) with an ODBC source (SQL Server) and a flat file destination configured for fixed-width columns with no row delimiter. The COMP-3 fields used SSIS's built-in DT_NUMERIC → packed decimal encoding. The PIC 9(n) display fields were pre-formatted to exact digit widths in the T-SQL SELECT before SSIS saw them, which kept the SSIS pipeline simple.
Why not just replace the COBOL batch layer immediately?
The COBOL batch programs are well-tested, FCA-compliant, and run without incidents. Replacing them requires re-implementing and re-certifying 30 years of business logic — arrears band calculation, Rule of 78 early settlement, FCA COBS reporting formats. DemoBank Finance's migration strategy was to stabilise the SQL Server schema first, prove it in parallel operation for two years, then tackle batch modernisation as a separate project with a separate budget. This is the rational approach for any regulated financial system.