SSIS developer on retainer: CLG_FreightOrderLoad Script Component IBM i fixed-width layout change adds 22 bytes — ORDER_QTY reads from wrong offset, silently loads zero for 6 weeks — 847 stock-out line items across 6 distribution centers

SQL Server Integration Services 2019 • Windows Server 2019 • Script Component source • IBM i fixed-width flat file • SFTP • C# position constants • int.TryParse • ORDER_QTY • FACT_ORDER_LINE • retainer billing

At Centaur Logistics Group, the nightly freight order load had been running without errors for three years when the supply chain team began noticing an unusual pattern: automated replenishment suggestions had dropped to zero across all six distribution centers. High-velocity SKUs were depleting without triggering reorders. By the time the purchasing manager escalated the issue in October 2026, 847 product line items had stock-out statuses across the DC network and backorders were accumulating. The replenishment algorithm was working correctly — it simply had no demand signal to work from. The SSIS developer retainer was engaged to investigate.

Centaur Logistics ran SQL Server Integration Services 2019 on Windows Server 2019. The central freight order ETL package, CLG_FreightOrderLoad.dtsx, ran nightly at 02:00 AM via SQL Server Agent. The package read daily freight order files from an IBM i (AS/400) source system delivered via SFTP to \\sftp\inbound\freight\orders_YYYYMMDD.txt, parsed the fixed-width records using a Script Component source, and loaded the resulting rows into FACT_ORDER_LINE in the Centaur data warehouse on SQL Server 2019. The Script Component source, named SC_IBM_i_Reader, had been in production since 2023. It had not been modified since its initial deployment. The IBM i flat file contained freight order records with a well-defined fixed-width layout: 10 fields per record, each at a specific byte offset and length defined in the IBM i file transfer specification version 4.2.

On August 12, 2026, the IBM i operations team released a new version of the freight order extract — layout version 4.3. The release added three new fields to the record layout to support a carrier priority management initiative: CARRIER_PRIORITY (6 characters), SERVICE_LEVEL (4 characters), and SPECIAL_INSTRUCTIONS (12 characters), inserted between the existing ORIGIN_CODE and DEST_CODE fields. The three new fields totaled 22 bytes. The IBM i team sent the updated layout specification to the EDI team, which filed it for future reference. The SSIS team was not notified. The Script Component source was not updated.

How the Script Component reads fixed-width records — and why the position constants matter

The SC_IBM_i_Reader Script Component implemented its own fixed-width parser in C#. The parser defined a set of integer constants representing each field’s starting byte offset in the record, then used string.Substring(offset, length) to extract each field’s value from the raw line string. The original layout version 4.2 constants were:

// SC_IBM_i_Reader Script Component — layout version 4.2 (correct for file format pre-August 12)
// Field extraction constants: (byte offset, length)

const int POS_ORDER_NUMBER    =  0; // len 10: order identifier
const int POS_SHIPPER_ID      = 10; // len  8: shipper account code
const int POS_ORIGIN_CODE     = 18; // len  6: origin terminal code
const int POS_DEST_CODE       = 24; // len  6: destination terminal code  ← SHIFTED after layout 4.3
const int POS_SHIP_DATE       = 30; // len  8: YYYYMMDD
const int POS_DELIVERY_DATE   = 38; // len  8: YYYYMMDD
const int POS_FREIGHT_CLASS   = 46; // len  4: NMFC freight class code
const int POS_ORDER_QTY       = 50; // len  8: integer quantity             ← SHIFTED after layout 4.3
const int POS_WEIGHT_LBS      = 58; // len 10: decimal weight
const int POS_DECLARED_VALUE  = 68; // len 12: decimal dollar value

// Layout version 4.3 (effective August 12, 2026):
// New fields inserted at offset 24, before DEST_CODE:
//   CARRIER_PRIORITY (offset 24, len  6)
//   SERVICE_LEVEL    (offset 30, len  4)
//   SPECIAL_INSTRUCTIONS (offset 34, len 12)
// DEST_CODE shifted: 24 → 46  (was 24, now 46)
// SHIP_DATE shifted:  30 → 52
// DELIVERY_DATE:      38 → 60
// FREIGHT_CLASS:      46 → 68
// ORDER_QTY:          50 → 72   ← C# constant still reads from 50 (FREIGHT_CLASS bytes)
// WEIGHT_LBS:         58 → 80
// DECLARED_VALUE:     68 → 90

After the August 12 layout change, every field whose constant was at offset 24 or higher was reading from the wrong byte range. The most critical impact was on ORDER_QTY: the constant POS_ORDER_QTY = 50 now pointed into the middle of the new SPECIAL_INSTRUCTIONS field (offset 34, length 12) and the beginning of the old DEST_CODE-now-shifted field. The 8-byte substring at position 50 in the new layout format contained alphabetic and symbolic characters from the special instructions text and terminal code fields — content that was not parseable as an integer.

The Script Component used int.TryParse to convert the extracted substring to an integer, with the result variable initialized to zero:

// SC_IBM_i_Reader: field extraction and assignment (excerpt from CreateNewOutputRows())
string rawLine = line;  // full record line from SFTP flat file

// Extract each field
string orderNumber   = rawLine.Substring(POS_ORDER_NUMBER,   10).Trim();
string shipperID     = rawLine.Substring(POS_SHIPPER_ID,      8).Trim();
string originCode    = rawLine.Substring(POS_ORIGIN_CODE,     6).Trim();
string destCode      = rawLine.Substring(POS_DEST_CODE,       6).Trim();  // reads 'STCOU' → carrier priority chars
string shipDateStr   = rawLine.Substring(POS_SHIP_DATE,       8).Trim();  // reads 'RD20260' → bad date

// ORDER_QTY: offset 50, length 8
// Layout 4.2 (pre Aug 12): rawLine[50..57] = '00000847' → int.TryParse OK → orderQty = 847
// Layout 4.3 (post Aug 12): rawLine[50..57] = 'RD202608' → portion of SPECIAL_INSTRUCTIONS + DEST_CODE
//   int.TryParse("RD202608", out int orderQty) → false → orderQty remains 0

int orderQty = 0;  // default: 0 if parse fails
if (!int.TryParse(rawLine.Substring(POS_ORDER_QTY, 8).Trim(), out orderQty))
{
    // TryParse returns false for non-numeric content.
    // No exception thrown. No error output. No log entry.
    // orderQty = 0 (default).
}

// Assign to output buffer
Row.OrderNumber   = orderNumber;
Row.ShipperID     = shipperID;
Row.OriginCode    = originCode;
Row.DestCode      = destCode;     // wrong value (carrier priority bytes)
Row.ShipDate      = ParseDate(shipDateStr);   // may fail → null
Row.DeliveryDate  = ParseDate(rawLine.Substring(POS_DELIVERY_DATE, 8).Trim());
Row.FreightClass  = rawLine.Substring(POS_FREIGHT_CLASS, 4).Trim();
Row.OrderQty      = orderQty;     // 0 for every record from August 12 onward
Row.WeightLbs     = ParseDecimal(rawLine.Substring(POS_WEIGHT_LBS, 10).Trim());
Row.DeclaredValue = ParseDecimal(rawLine.Substring(POS_DECLARED_VALUE, 12).Trim());

The SSIS Data Flow ran without errors. The Script Component produced the correct number of output rows — 2,847 per day (matching the IBM i file row count). The OLE DB Destination inserted all 2,847 rows into FACT_ORDER_LINE successfully. The SQL Server Agent job completed with RC=0 every night from August 12 onward. The SSIS Catalog Execution Report showed no warnings, no error output rows, and no failed components. The only sign of the problem was the data itself: ORDER_QTY = 0 for every row loaded since August 12. That condition was invisible until the downstream replenishment algorithm had been consuming zero-demand data long enough to deplete physical stock.

Diagnosis: FACT_ORDER_LINE value inspection, SSIS Execution Report, and IBM i layout specification

The SSIS developer retainer began diagnosis on October 23, 2026. The first step was checking FACT_ORDER_LINE directly:

-- Step 1: Confirm ORDER_QTY values in FACT_ORDER_LINE
-- SQL Server 2019 CLG_DW database, executed October 23 2026

SELECT
    MIN(LoadDate) AS FirstLoadDate,
    MAX(LoadDate) AS LastLoadDate,
    COUNT(*)      AS RowCount,
    SUM(OrderQty) AS TotalUnits,
    AVG(CAST(OrderQty AS FLOAT)) AS AvgOrderQty
FROM FACT_ORDER_LINE
GROUP BY CAST(LoadDate AS DATE)
ORDER BY CAST(LoadDate AS DATE) DESC;

-- Results (selected rows):
-- LoadDate     RowCount  TotalUnits  AvgOrderQty
-- 2026-10-22   2,847     0           0.00   ← all zero
-- 2026-10-21   2,847     0           0.00
-- ...
-- 2026-08-13   2,847     0           0.00   ← first zero-filled day
-- 2026-08-12   2,847     0           0.00   ← first bad load (same day as layout change)
-- 2026-08-11   2,847     4,293,847   1,508  ← last correct load
-- 2026-08-10   2,847     4,187,293   1,471  ← correct

-- Observation: ORDER_QTY = 0 for ALL 119,574 records from August 12 through October 22 (42 days).
-- Last correct load: August 11, 2026.

-- Step 2: Sample a raw record from the August 12 SFTP file
-- Archived at \\sftp\archive\freight\orders_20260812.txt

-- Record 1 (raw line, truncated for display):
-- Position: 0         10        20        30        40        50        60        70
--           |         |         |         |         |         |         |         |
-- Content:  CLG0000001SHPR0047  DALPHL    STDCORE   RD20260812202608200123   00000847...
--                               ^^ORIGIN  ^^CARRIER_PRIORITY (new)          ^^ORDER_QTY (now at 72)
--                                                            ^^SERVICE_LEVEL

-- At offset 50, length 8: "RD202608" — first 2 chars of SPECIAL_INSTRUCTIONS + partial content
-- int.TryParse("RD202608") = false → ORDER_QTY = 0

-- At offset 72, length 8: "00000847" — correct ORDER_QTY for this record
-- int.TryParse("00000847") = true → orderQty = 847

The second diagnostic step confirmed the precise date of the layout change and the specific fields affected:

-- Step 3: Compare IBM i layout specifications
-- Layout version 4.2 (from CLG EDI specifications archive, dated January 2023):
--   Field 1: ORDER_NUMBER       offset  0, length 10
--   Field 2: SHIPPER_ID         offset 10, length  8
--   Field 3: ORIGIN_CODE        offset 18, length  6
--   Field 4: DEST_CODE          offset 24, length  6
--   Field 5: SHIP_DATE          offset 30, length  8
--   Field 6: DELIVERY_DATE      offset 38, length  8
--   Field 7: FREIGHT_CLASS      offset 46, length  4
--   Field 8: ORDER_QTY          offset 50, length  8
--   Field 9: WEIGHT_LBS         offset 58, length 10
--   Field 10: DECLARED_VALUE    offset 68, length 12
--   Total record length: 80 bytes

-- Layout version 4.3 (from IBM i team email attachment, dated August 8 2026):
--   Field 1: ORDER_NUMBER       offset  0, length 10
--   Field 2: SHIPPER_ID         offset 10, length  8
--   Field 3: ORIGIN_CODE        offset 18, length  6
--   Field 4: CARRIER_PRIORITY   offset 24, length  6  ← NEW
--   Field 5: SERVICE_LEVEL      offset 30, length  4  ← NEW
--   Field 6: SPECIAL_INSTRUCTIONS offset 34, length 12 ← NEW
--   Field 7: DEST_CODE          offset 46, length  6  (was 24, shifted +22)
--   Field 8: SHIP_DATE          offset 52, length  8  (was 30, shifted +22)
--   Field 9: DELIVERY_DATE      offset 60, length  8  (was 38, shifted +22)
--   Field 10: FREIGHT_CLASS     offset 68, length  4  (was 46, shifted +22)
--   Field 11: ORDER_QTY         offset 72, length  8  (was 50, shifted +22) ← CRITICAL
--   Field 12: WEIGHT_LBS        offset 80, length 10  (was 58, shifted +22)
--   Field 13: DECLARED_VALUE    offset 90, length 12  (was 68, shifted +22)
--   Total record length: 102 bytes (was 80)

-- Root cause confirmed: all fields from DEST_CODE (originally offset 24) onward
-- shifted by 22 bytes on August 12. C# constants not updated.

Diagnosis was complete in 1.0 hour. The IBM i layout change email was found in the EDI team’s shared mailbox, dated August 8, 2026 — four days before the layout change took effect.

The fix: updating C# position constants and reloading 42 days of backlog

The fix required two components: updating the Script Component’s C# position constants to match layout version 4.3, and reprocessing all 42 days of archived SFTP files to correct the zero-filled ORDER_QTY values in FACT_ORDER_LINE.

Part 1: Update position constants. All seven constants at offset 24 or above were updated to reflect the +22-byte shift. Three new constants were added for the new fields:

// SC_IBM_i_Reader — updated for IBM i layout version 4.3 (effective August 12, 2026)

const int POS_ORDER_NUMBER        =  0; // len 10 — unchanged
const int POS_SHIPPER_ID          = 10; // len  8 — unchanged
const int POS_ORIGIN_CODE         = 18; // len  6 — unchanged
const int POS_CARRIER_PRIORITY    = 24; // len  6 — NEW (layout 4.3)
const int POS_SERVICE_LEVEL       = 30; // len  4 — NEW (layout 4.3)
const int POS_SPECIAL_INSTRUCTIONS= 34; // len 12 — NEW (layout 4.3)
const int POS_DEST_CODE           = 46; // len  6 — was 24, shifted +22
const int POS_SHIP_DATE           = 52; // len  8 — was 30, shifted +22
const int POS_DELIVERY_DATE       = 60; // len  8 — was 38, shifted +22
const int POS_FREIGHT_CLASS       = 68; // len  4 — was 46, shifted +22
const int POS_ORDER_QTY           = 72; // len  8 — was 50, shifted +22
const int POS_WEIGHT_LBS          = 80; // len 10 — was 58, shifted +22
const int POS_DECLARED_VALUE      = 90; // len 12 — was 68, shifted +22

The three new fields (CARRIER_PRIORITY, SERVICE_LEVEL, SPECIAL_INSTRUCTIONS) were added as output columns to the Script Component’s output buffer definition, with corresponding columns added to the OLE DB Destination’s target table FACT_ORDER_LINE via an ALTER TABLE statement. The updated SSIS package was recompiled in SSDT, deployed to the SSIS Catalog (SSISDB), and tested against a sample SFTP file from October 22. The test loaded correctly: ORDER_QTY = 847 for the first record, matching the raw file content at offset 72.

Part 2: Reload 42 days of archived SFTP files. SFTP archive files for August 12 through September 22, 2026 (42 days × 2,847 records = 119,574 records) were available in \\sftp\archive\freight\. A SQL Server Agent job was created to execute CLG_FreightOrderLoad.dtsx sequentially for each archived file by setting the package’s SourceFilePath parameter to each archive filename. Existing rows in FACT_ORDER_LINE for those 42 load dates were deleted before reload via a pre-execute SQL task:

-- Pre-reload cleanup: delete incorrect zero-filled rows for August 12 through September 22
DELETE FROM FACT_ORDER_LINE
WHERE LoadDate >= '2026-08-12' AND LoadDate <= '2026-09-22';
-- Rows deleted: 119,574

-- Backlog reload via SQL Agent job: 42 iterations
-- For each date d in 2026-08-12 through 2026-09-22:
--   Execute CLG_FreightOrderLoad.dtsx with SourceFilePath = \\sftp\archive\freight\orders_YYYYMMDD.txt
--   Verify row count = 2,847 and SUM(OrderQty) > 0 for that load date

-- Backlog reload runtime: 47 minutes (42 package executions × ~67 seconds each)
-- Total rows reloaded: 119,574

-- Post-reload validation:
SELECT CAST(LoadDate AS DATE) AS LoadDate, COUNT(*) AS Rows, SUM(OrderQty) AS TotalQty
FROM FACT_ORDER_LINE
WHERE LoadDate >= '2026-08-12' AND LoadDate <= '2026-09-22'
GROUP BY CAST(LoadDate AS DATE)
ORDER BY CAST(LoadDate AS DATE);
-- All 42 dates: Rows = 2,847, TotalQty > 0 (range: 3,847,293 to 4,847,293 per day) ✓

After the backlog reload, the replenishment algorithm was re-run against the corrected FACT_ORDER_LINE data for all 42 previously-affected load dates. The algorithm computed correct average daily demand for all affected SKUs, generated emergency reorder quantities for the 847 stock-out line items, and submitted purchase orders to the carrier network. Total engagement time: 4.5 hours (1.0h diagnosis: FACT_ORDER_LINE ORDER_QTY inspection, SSIS Catalog execution report review, raw SFTP file byte-level comparison, IBM i layout specification retrieval; 1.5h fix: position constant updates, new field output column additions, FACT_ORDER_LINE ALTER TABLE, SSDT recompile and SSIS Catalog redeployment, sample file test; 1.5h backlog reload: pre-cleanup SQL, 42-iteration SQL Agent job design and execution, post-reload validation; 0.5h documentation and retainer SOP update).

Why Script Component fixed-width layout maintenance is a recurring SSIS retainer problem

The Centaur Logistics incident is a representative example of the most common SSIS retainer correctness failure: a Script Component that parses an upstream fixed-width file using hardcoded position constants does not self-detect when those constants become incorrect. The upstream system (IBM i, AS/400, mainframe, or any legacy flat-file generator) changes its record layout, and the Script Component continues to run successfully — it simply reads the wrong bytes for every field whose position shifted.

Three properties of this failure class make it difficult to detect without a dedicated retainer review. First, SSIS reports success: the Script Component generates the correct number of output rows, the OLE DB Destination inserts all rows, and the SQL Agent job completes with RC=0. SSIS has no mechanism to validate that a Script Component’s extracted values are semantically correct — only that the data flow processed all rows without an exception. Second, the int.TryParse pattern used in the CVSD Script Component is correct defensive coding: it prevents an unhandled exception if a numeric field contains unexpected content. But when used without logging the parse failure, it makes the corruption silent. A version of this Script Component that logged every TryParse failure would have generated 2,847 warnings on August 12 — visible in the SSIS Catalog execution report — and the retainer would have been engaged the same day. Third, the affected field was a quantity field that defaulted to zero when parsing failed. Zero is a valid business value in many ETL contexts (a shipment with zero line items might be a valid edge case). A replenishment algorithm that treated zero-quantity orders as legitimate data would not flag the anomaly immediately.

An SSIS developer retainer who reviews Script Component source code quarterly — specifically checking that hardcoded position constants match the current version of the upstream layout specification for every fixed-width file source — catches this class of issue before a layout change takes effect. The IBM i team issued layout version 4.3 four days before its effective date: a retainer who received that notification and audited the Script Component on August 9 would have prevented all 42 days of corrupted load data and all 847 stock-out line items.

How HourTab helps SSIS developer retainers

The Centaur Logistics Group engagement totaled 4.5 hours: diagnosis (1.0h — 0.25h querying FACT_ORDER_LINE for ORDER_QTY values by load date, confirming all zero from August 12 through October 22; 0.25h reviewing SSIS Catalog execution reports for CLG_FreightOrderLoad for the same date range, confirming RC=0 all 42 runs with correct row counts and no error output rows; 0.25h byte-level inspection of the August 12 SFTP archive file, extracting bytes 50–57 and confirming non-numeric content vs bytes 72–79 showing correct ORDER_QTY value; 0.25h retrieving IBM i layout specification v4.3 from EDI team shared mailbox, confirming 22-byte shift for all fields after ORIGIN_CODE), fix (1.5h — 0.5h updating 7 existing position constants and adding 3 new constants in SC_IBM_i_Reader Script Component source code; adding CARRIER_PRIORITY, SERVICE_LEVEL, SPECIAL_INSTRUCTIONS output columns to Script Component output buffer definition; running ALTER TABLE FACT_ORDER_LINE to add the 3 new columns; 0.5h SSDT package recompile, ISPACK build, SSIS Catalog project deployment, and sample-file test confirming ORDER_QTY = 847 for the first archive record; 0.5h constructing 42-iteration SQL Agent backlog reload job with pre-execute delete and post-execute row count validation), backlog reload (1.5h — monitoring 42-package execution sequence, confirming each date loaded 2,847 rows with nonzero ORDER_QTY totals, and coordinating with supply chain team to trigger replenishment algorithm rerun), and documentation (0.5h — post-incident write-up for retainer file; retainer SOP updated to add quarterly Script Component layout specification audit; IBM i change notification distribution list updated to include SSIS developer retainer).

In a HourTab retainer log, the incident session reads: “CLG_FreightOrderLoad SC_IBM_i_Reader fixed-width position constants stale; IBM i layout v4.3 (August 12 2026) inserted CARRIER_PRIORITY(6) + SERVICE_LEVEL(4) + SPECIAL_INSTRUCTIONS(12) = 22 bytes before DEST_CODE; POS_ORDER_QTY=50 now reads SPECIAL_INSTRUCTIONS+DEST_CODE bytes instead of ORDER_QTY; int.TryParse('RD202608', ...) = false → orderQty=0; FACT_ORDER_LINE.ORDER_QTY=0 for all 119,574 records Aug 12–Sep 22 (42 days); replenishment algorithm: zero avg demand → zero reorder suggestions → 847 stock-out items across 6 DCs; fix: updated all 7 shifted constants + 3 new constants for new fields; SSDT recompile + SSIS Catalog redeploy; 42-day backlog reload from SFTP archive (119,574 rows); FACT_ORDER_LINE ORDER_QTY correct; replenishment rerun; emergency POs submitted; 4.5h.” The Centaur Logistics supply chain director sees the root cause (IBM i layout change not communicated to SSIS team), the scope of impact (42 days, 119,574 records, 847 stock-out items), and the fix applied at both layers (position constant correction and full backlog reload) — with no ambiguity about how the retainer time was spent.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: SSIS developer retainers

See also: Azure Data Factory developer retainer · Informatica developer retainer · DataStage developer retainer