SSIS Custom logging for Incremental ETL

Here we will list steps to build a custom ETL logging in SSIS:

– Start by defining logging parameters:

– Add row counts to your data flow task and link them to 2 parameters: Extract_RowCount and Load_RowCount:

– Test your RowCounts:

image11

– Design a variable DynamicStatsQuery and assign it an expression that build the logging query from the source table:
image12

Query:
“SELECT IFNULL(COUNT(*),0) PreETL_RowCount, IFNULL(MIN(Customer_id),0) AS MinID, IFNULL(MAX(Customer_id),0) AS MaxID
FROM customer
WHERE IFNULL(serial_id,”) <> ” AND customer_id = 0 AND occ_customer_id > “+ (DT_WSTR, 100) @[User::MaxLastID]

– Build a logging task after the Latest DW Key pickup and before the main ETL task, and that reads that query as its SQL statement and link it to the different parameters:

image13

– Set its Result Set to output the three variables: PreETL_RowCount, MinID, MaxID:

image18

– Design and install the Logging Table:

CREATE TABLE [dbo].[ETL_Logs](
[ID] [int] IDENTITY(1,1),
[table “” not found /]
[nchar](50) NULL,
[LastMaxID] [bigint] NULL,
PreETL_CountRows [int] NULL,
ETL_MinID [int] NULL,
ETL_MaxID [int] NULL,
[Extracts] [int] NULL,
[Loads] [int] NULL,
[ETL_Date] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [ETL_Logs] ADD DESTINATION_SERVER SYSNAME
GO

image15

– Build a SQL task after the main ETL task: Log Stats, and configure its query as follows:

image17

Query:
insert into Olympus_ETL_Logs ([table “” not found /]
, LastMaxID, PreETL_CountRows, ETL_MinID, ETL_MaxID, Extracts, Loads, ETL_Date, DESTINATION_SERVER)
values (‘occ_customer’, ?, ?, ?, ?,?,?, getdate(), ?)

– Add the populated parameters to logging task:
image16

– Test your ETL process logging by running your ETL and then querying your log table:
image14

Leave a Reply

Your email address will not be published. Required fields are marked *