Sometimes the need arises to delete rows from the CDC Staging Area. The following process can be used to delete transactions from the staging area using a windows batch file.
NOTE: In order for the following to work the context column in the staging area tables needs to use the standard context. The default for SSIS Solutions is to populate the context column with the agent context. To Change the default export then machine configuration and change the Router temp feature 'routerUseAgentContextInContext' from ‘true’ to ‘false’ and then import the configuration.
Place the following into a .bat file. The usage help is defined at the bottom of the text.
@echo off
if "%5"=="" goto USAGE
REM Create the select query...
echo select ''''^|^|max(C)^|^|'''' max_ctx from (select context C from %3 ^<access (without scan)^> where context ^< > %3_GetMaxContext.sql
echo year(DATEADD(hour,-%4,now()))^|^| >> %3_GetMaxContext.sql
echo substr( 'x'^|^|'00'^|^|month(DATEADD(hour,-%4,now())),len('x'^|^|'00'^|^|month(DATEADD(hour,-%4,now()))) - 1) ^|^| >> %3_GetMaxContext.sql
echo substr( 'x'^|^|'00'^|^|day(DATEADD(hour,-%4,now())),len('x'^|^|'00'^|^|day(DATEADD(hour,-%4,now()))) - 1) ^|^|'T'^|^| >> %3_GetMaxContext.sql
echo substr( 'x'^|^|'00'^|^|hour(DATEADD(hour,-%4,now())),len('x'^|^|'00'^|^|hour(DATEADD(hour,-%4,now()))) - 1) ^|^| >> %3_GetMaxContext.sql
echo substr( 'x'^|^|'00'^|^|minute(DATEADD(hour,-%4,now())),len('x'^|^|'00'^|^|minute(DATEADD(hour,-%4,now()))) - 1) >> %3_GetMaxContext.sql
echo limit to %5 rows) T; >> %3_GetMaxContext.sql
echo.
echo Deleting from table %3, %5 rows at a time, records older than %4 hours
REM Start the loop of delete queries...
:LOOP
REM Create the delete query...
echo delete from %3 where context ^< > %3_DeleteOldContextRows.sql
nav_util -nowait -b %1 execute %2 %3_GetMaxContext.sql |find "'" >> %3_DeleteOldContextRows.sql
REM Delete the rows...
nav_util -nowait -b %1 execute %2 %3_DeleteOldContextRows.sql |find "affected"
if errorlevel 1 goto EXIT
goto LOOP
goto EXIT
:USAGE
echo.
echo.
echo Usage:
echo.
echo cleanup BindingName DatasourceName TableName ExpirationHours DeleteCount
echo.
echo Arguments:
echo ==========
echo BindingName - The Staging Area binding name.
echo DatasourceName - The Staging Area datasource name.
echo TableName - The Staging Area table to delete old rows from.
echo ExpirationHours - Delete rows that are older than these many hours.
echo DeleteCount - Number of rows to delete in one pass.
echo.
:EXIT
echo Done!