SSIS: Enhancing the reusability of SSIS Package processes by setting checkpoints checkpoints

Usually an ETL Package is composed of multiple control flow and data flow, sometimes the steps of ETL may be more, the whole process can be carried out for a long time. Assuming that there are 5 tasks in the ETL Package, the first 3 tasks execute more than 1 hours and fail at the 4th task. If you start again with a 1th task at the next execution, you will have to spend another 1 hours waiting for the 1-3 task to execute, which is certainly very inefficient in terms of efficiency. Especially in the application of data Warehouse, often from the data source to the staging process, there are tens or even billions of data to load, after loading and then into the dimension and fact table. If a failure occurs in the process of entering the dimension and the fact table, it means that the next time you need to reload the data into the staging table. And through the checkpoint checkpoint settings can solve this problem, through reasonable settings can skip the last time has successfully performed some steps and directly from the failure to restart execution, which greatly improve the efficiency of the package execution.

The following ETL example simply simulates the process of extracting data from a data source and then outputting it to a datasheet and a flat file, before looking at the use of CheckPoint.

Use biwork_ssis
go
    
if object_id (' ck_address ') are not NULL
DROP TABLE ck_address 
go
    
if object_id (' Ck_addressaudit ') is not a NULL
DROP TABLE ck_addressaudit go
    
CREATE table ck_address
(
    addressid INT,
    AddressLine1 NVARCHAR,
    AddressLine2 NVARCHAR, City
    NVARCHAR ()
    
CREATE TABLE ck_addressaudit
(
    ID INT PRIMARY KEY IDENTITY (1,1),
    cityname NVARCHAR (),
    Counts INT
)

ETL Process-

Est_truncateaddress-truncate TABLE dbo. Ck_address TRUNCATE TABLE dbo. Ck_addressaudit

Dft_loadaddress-Extracts data from AdventureWorks2012.People.Address to dbo. In ck_address

Dft_saveaddressaudit-makes some statistics on the data in the Ck_address table and writes to the dbo. In Ck_addressaudit

Dft_outputaddressaudit-will dbo. The data in the Ck_addressaudit is exported to a text file