How to perform batch processing in SQL Server Integration Services (SSIS)

Problem

We have been loading data warehouses for many years, each loaded with a real record. We want to execute this process in batches and be able to restart the failed point of failure in the event of an error. Can you give us an example of how we could perform this batch function in an SSIS package?

Expert answers

SSIS uses the existing components in Toolbox to support batch processing well. An easy way to perform batch processing in SSIS is to put records grouped into batches, process each batch, and then update each group to be processed. Let's first sketch a scene and then execute a SSIS package to do the job.

A common requirement in developing report applications is aggregation of data so that report queries can be executed quickly. Let's say we want to aggregate the data by month. We also want to have the ability to adjust aggregated data and only recalculate the adjusted monthly aggregation.

We can assume that an SSIS package has the following steps:

The Get Batch list is an Execute SQL task that groups the source data in batches and creates a result set that contains a single record in each batch.

The process Batch loop (executing a batch loop) is a Foreach Loop container that iterates through the result set record; For example, each record in this result set is executed once.

The Transaction Container (transaction container) is a sequence container that contains the tasks to be performed by each iteration of the loop, which controls the transaction, commits if it succeeds, and rolls back if it fails.

Append Batch to Sales History (additional batch to sale history) is an Execute SQL task that extracts a batch of records and inserts them into a history table.

Compute Aggregation (Compute aggregation) is an Execute SQL task that aggregates the batch and updates the aggregation table.

Mark Batch as processed (marked batch processing) is an Execute SQL task that updates records in the source table to show that they have been processed.

In the following sections, we will discuss each of the steps in the SSIS package in detail. Start with the build, and then introduce it step-by-step.