The Mystery of a Million Missing Records: Why Incremental Loading Failed in SSIS and DWH

When Month-End Reporting Became an Emergency

Friday morning, the last day of the month. For finance and controlling departments, this is one of the most critical periods of the reporting cycle.

Our team received an urgent request: data in the customer's data warehouse had stopped progressing as expected. New records appeared to be flowing from the ERP system, but older and accumulated batches remained stuck in the extraction layer. The downstream staging process was not picking them up.

The customer needed an immediate investigation of the SSIS solution and a reliable way to move the missing records through the pipeline.

Rather than making unverified changes directly in production, our team first focused on reproducing the environment safely and tracing the data flow step by step.

Understanding the Data Flow: Where Did the Records Get Stuck?

The data warehouse used a conventional three-layer architecture fed by Microsoft Dynamics 365 Business Central.

Figure 1. Data flow from the ERP system through extraction and staging into the data warehouse.

The transfer from the ERP system into the EXT layer was working. However, the SSIS process responsible for moving records from EXT into the consolidated SA staging table was silently excluding certain sets of records.

This distinction was important. The data had not disappeared from the source. It existed in the extraction layer but did not reach the next processing stage.

The DBA Rule: Restore First, Investigate Safely

Debugging complex SSIS packages and running potentially expensive diagnostic queries directly against a production data warehouse during month-end closing would introduce unnecessary risk.

Our team therefore initiated a backup and restore of the relevant production databases into a dedicated test environment.

Measuring the Missing Data

Once the investigation environment was prepared, the next step was to determine the actual extent of the missing records.

Our team compared the extraction table of one subsidiary with the consolidated staging table, using NOT EXISTS to identify records that had not reached SA.

SELECT posting_date, COUNT(*) AS MissingRowsCount
FROM EXT.dbo.T_CTR_Value_Entry_COMPANY_ALPHA ext
WHERE NOT EXISTS (
    SELECT 1 FROM SA.dbo.S_CTR_Value_Entry sa
    WHERE ext.Company = sa.Company
      AND ext.Entry_No_ = sa.Entry_No_
)
AND posting_date >= CONVERT(date, '2025-10-22')
GROUP BY posting_date
ORDER BY posting_date;

The results confirmed the scale of the problem. More than one million records were missing from the staging layer for a single subsidiary alone.

Those records were present in EXT, but they had never been transferred to SA. The investigation could now move beyond symptoms and focus on the SSIS processing logic.

Finding No. 1: A Four-Minute Wait for a Single Value

While examining the SSIS package responsible for loading value-entry data, our team identified a task named Retrieve max value from target table.

The task executed a seemingly straightforward query:

SELECT MAX(LoadId) AS MaxLoadID
FROM SA.dbo.S_CTR_Value_Entry;

Despite its simplicity, the query took three to four minutes during production executions.

The reason was a missing supporting index on LoadId in the staging table. SQL Server repeatedly had to scan the table to determine the maximum value.

After adding the appropriate index, execution time dropped to a few milliseconds.

This removed a significant performance bottleneck, but it did not explain why certain records were being excluded from the loading process.

Finding No. 2: The Global Incremental Watermark

The more serious problem was hidden in the package’s incremental loading logic.

• Retrieve MAX(LoadId) from the consolidated staging table.
• Store the result in the variable v_Incremental.
• Dynamically generate a UNION ALL query across company-specific extraction tables.
• Apply the same incremental filter to every company:

WHERE LoadId > @v_Incremental

The key assumption was that a single global maximum could safely determine which records were new across all subsidiaries.

However, LoadId values were not coordinated by a single global sequence across companies. Each subsidiary had its own extraction table, and the tables received data at different rates.

• Company B had already generated LoadId values up to 5,200,000.
• The consolidated staging table therefore returned a global maximum of 5,200,000.
• Company A still had unprocessed records with LoadId values between 1,500,000 and 4,800,000.
• Applying WHERE LoadId > 5200000 to Company A excluded those records, even though they had not yet been loaded into staging.

The SSIS package could finish successfully while silently skipping valid records.

Evidence from the Extraction Tables

The following screenshots demonstrate how the global watermark affected different company-specific data sets.

Figure 2. Incremental loading check showing 1,090,918 records that did not meet the global LoadId watermark condition in one extraction table.

In the first query, no records satisfied the condition LoadId > @global_wm, while 1,090,918 records fell below or at the global watermark.

Figure 3. The same global watermark check against another company-specific extraction table, showing 696,040 records below or equal to the threshold.

A second extraction table produced a similar result, with 696,040 records below or equal to the global threshold and none above it.

These results show how a shared incremental watermark could exclude large groups of records from multiple company-specific sources.

The Problem in the SSIS Script

The dynamic SQL generation logic confirmed that the same incremental variable was appended to the combined query.

Figure 4. SSIS Script Task generating a UNION ALL query and applying a single LoadID threshold through the v_Incremental variable.

The highlighted part of the script shows the critical logic:

sqldinamico = sqldinamico & ") a " & vbCrLf &
    "WHERE LoadID > " &
    Dts.Variables("v_Incremental").Value.ToString

Rather than evaluating each company’s progress independently, the package applied one shared threshold to the combined data set. That was the underlying design flaw.

Proposed Solution: Separate Incremental Tracking by Company

Instead of repeatedly correcting individual missing batches, our team proposed changing the SSIS query generation logic.

Each subsidiary should use its own incremental threshold, derived from the corresponding records already present in the staging layer.

A simplified example illustrates the proposed approach:

-- Company ALPHA
SELECT *
FROM EXT.dbo.T_CTR_Value_Entry_COMPANY_ALPHA
WHERE company_name = 'COMPANY_ALPHA'
  AND posting_date >= @DateWatermark
  AND LoadId > (
      SELECT MAX(LoadId)
      FROM SA.dbo.S_CTR_Value_Entry
      WHERE company_name = 'COMPANY_ALPHA'
  )
UNION ALL
-- Company BETA
SELECT *
FROM EXT.dbo.T_CTR_Value_Entry_COMPANY_BETA
WHERE company_name = 'COMPANY_BETA'
  AND posting_date >= @DateWatermark
  AND LoadId > (
      SELECT MAX(LoadId)
      FROM SA.dbo.S_CTR_Value_Entry
      WHERE company_name = 'COMPANY_BETA'
  );

With this approach, a subsidiary processing fewer transactions would no longer be affected by the higher LoadId values of another subsidiary.

The incremental watermark would reflect the progress of each company independently.

The proposed change addressed the underlying flaw in the incremental loading logic by tracking each company's progress independently. At the time of the investigation, the long-term production results had not yet been verified.

Key Lessons for Data Engineers and DBAs

• Incremental watermarks must match the granularity of the data. In a multi-company environment, a global watermark is unsafe when source identifiers are generated independently.
• ETL control columns need appropriate indexes. Columns used for MAX(), MIN(), and incremental filtering should have supporting indexes based on the actual query patterns.
• Validate missing records explicitly. Comparing source and destination keys with NOT EXISTS helps identify affected entities and periods instead of relying solely on successful SSIS execution statuses.
• Treat every ETL change as production-critical. Even a common programming mistake in an SSIS load can silently corrupt or omit data. All changes must pass structured testing and validation before deployment, regardless of time pressure or other demands, because rushed releases significantly increase the risk of human error.
• Investigate safely outside production. Restoring databases into a test environment allows detailed SSIS analysis without introducing additional risk during business-critical processing periods.

SMT Stories Takeaway

An incremental loading process is only as reliable as its watermark logic. When data sources maintain independent sequences, tracking progress globally can silently exclude valid records even when every ETL job reports success. This incident also shows how an ordinary programming mistake can compromise data quality: every SSIS change must therefore be thoroughly tested and validated before production deployment, regardless of time pressure or other demands.