Troubleshooting Missing Data during Full Load.

[David_Fergen]

Description

The missing data problem in Full Load is not common, and can be divided into two categories:

1. Full Load only Tasks
2. Full Load and CDC

The Full Load only Tasks are very straightforward and the main issue here if the "missing data" is related to actual failures or data changes which occurred during the Full Load. When adding CDC, the cached changes should also be taken into account.

In many systems some of the tables may include one timestamp field or more, this identifies the actual creation date/time of the record, and at times the last modified date/time. This actually depends on the design of the tables, and may exists when the database serves a software package like CRMs, ...
 
These fields will make it easier debug such issues, giving us a clear indication of time.
 
Suggested approach:

• Select one of the simple table(s) for the debugging purpose, make sure it includes timestamp fields.
• Review the start time of the Full Load from the Task’s Full Load Monitor view, or review the log file.
• Review the log file of any failures related to load process.

Hints:

• Search for errors or warnings related to the Load.
• Search for data and truncation errors.
• Review the Source and Target table DLLs:
  • Data type differences.
  • Nullability issue.
  • Primary Key and Unique Indexes differences.

 
Cached Events
 
When the Task is both Full Load and CDC, an intermediate process called “Cached Events” will be in use. Cached events are those events captured during the Full Load. These will be applied immediately after the Full Load finishes and just before starting the CDC process.
 
If the timestamp of the records indicate that the missing data is related to the cached events. It's important to review the log file for any failures.

Information Required

To help to debug such problem, please a package with the following information:

1. Export of the Task(s).
2. Log file(s) of the Full Load process.
3. Metadata of the table(s) from the Source and the Target.
4. Example(s) of missing data records.

If the problem is reproducible add the following logging and attach new log:

• SOURCE_UNLOAD to Verbose.
• TARGET_LOAD to Verbose.