ETL Design for Missing Data

Here is a way to create an SSIS that audits the transfer of data each step of the way. The main transformations used for this are:

• Row Count
  
  • The row count was used to validate the number of records
• Conditional Split
  • Was used to split out rows with a specific Header value. In this case “H”
• Aggregate
  • Was used to sum the specific values

Here are the results. I used data viewers to confirm the specific rows:

Here are the results of the aggregate: