It is no longer necessary to run this script. We have added a feature in the execution package, to full load specified tables. You just add a new execution package and add the incremental load tables you want to fully load.
If you still want to use the old script, it is described below.
If you want to do a periodical full load of all tables in a database, the script below will trigger it when executed.
You add the script as a Script Action. Right click on a business unit, go to Advanced and select Set Pre and Post Scripts. Then you can select the script in the pre section (first dropdown). This will execute the script before the business unit is executed.
IF DATEPART(dw,GETDATE())= 7 -- 7 is Saturday, 1 is Sunday BEGIN DECLARE @sql NVARCHAR(2000) SET @sql = 'DECLARE @tblName varchar(128), @sqlInline nvarchar(2000), @postFix varchar(2) SET @postFix = ''_V'' SET @tblName = ''?'' IF @tblName LIKE ''%_INCR]'' BEGIN SET @sqlInline = ''TRUNCATE TABLE '' + @tblName + ''; TRUNCATE TABLE '' + SUBSTRING(@tblName, 1, LEN(@tblName)-6) + @postFix + '']'' EXEC sp_executesql @sqlInline END ' EXEC sp_MSForEachTable @command1 = @sql END
Notes
The script works for NAV Adapters. If you want to use this for a normal data source, you must replace %_INCR] with %_I]
If the script is used for other data sources than NAV, you should also change the parameter from 6 to 3 in the LEN-part so the entire line reads as follows:
TRUNCATE TABLE '' + SUBSTRING(@tblName, 1, LEN(@tblName)-3) + @postFix + '']''
This article applies to: timeXtender 4.5, tX2012, TX2014.
13 Comments