Overview
Setting a Full Load on tables that are incrementally loaded is supported natively in the Jet Data Manager. This is covered in the KB article here: Creating an Execution Package. However there may be times where more control is needed over when the full load is done. The process below outlines the steps to set up a script that will trigger a full load on all incrementally loaded tables in the data warehouse and staging database.
Process
The script below will automatically truncate all _I and _V tables in the data warehouse and staging database which will, in turn, trigger a full load to be processed on all of these tables during the execution process.
This script should be added in an appropriate place as a script action on the staging database and the database name highlighted in yellow below should be replaced with the name of the data warehouse SQL database.
DECLARE
@SqlCommandStage nvarchar(2000)
,@SqlCommandDwh nvarchar(2000)
-- Stage
SET @SqlCommandStage =
'DECLARE @SqlString nvarchar(2000)
IF ''?'' LIKE ''%_INCR]''
BEGIN
SET @SqlString = ''TRUNCATE TABLE ?; TRUNCATE TABLE '' + SUBSTRING(''?'', 1, LEN(''?'') - 5) + ''V];''
EXEC sp_executesql @SqlString
END'
-- Data Warehouse
SET @SqlCommandDwh =
'DECLARE @SqlString nvarchar(2000)
IF ''?'' LIKE ''%_I]''
BEGIN
SET @SqlString = ''TRUNCATE TABLE ?; TRUNCATE TABLE '' + SUBSTRING(''?'', 1, LEN(''?'') - 2) + ''V];''
EXEC sp_executesql @SqlString
END'
IF DATEPART(dw, GETDATE()) = 7
BEGIN
EXEC [sys].[sp_MSforeachtable] @command1 = @SqlCommandStage
EXEC [ JetNavDwh ].[sys].[sp_MSforeachtable] @command1 = @SqlCommandDwh
END
Comments