Ok. From what I understand of your needs here's what I would do in your shoes, Kris, other than walk funny that is.
Create a job that just
truncates your table and does nothing more. This is perfectly valid and something I've done many times before. All you need is a transformer and a database stage. Create a dummy
stage variable in the transformer so the compiler is happy, one you don't even need to use. Put at least one column from the table in the link between the two stages. Set your constraint to
@FALSE and your Update Action to 'Truncate then insert'. As noted earlier, no rows will go down the link - it will simply truncate the table when run.
Create a parameter in your Sequence job to control this, something like TruncateTable let's say. Parameter type is
List with valid values of 'Y' and 'N' - make sure you
default it to 'N' so that someone has to explicitly switch it to 'Y' to make this happen. Note these two values could be anything you desire to check for, even "No, don't even think about truncating!" and "Oh My Gawd, are you really *really* sure you want to truncate them tables?".
Start your jobstream off with a
Nested Condition stage, the one with the question mark that allows you to make 'decisions' in your stream. Use it to either run or branch around the truncate job by checking the job parameter. Use two triggers:
Code: Select all
TruncateTable='Y' Links to the truncate job immediately after the NC stage
TruncateTable='N' Links to a Sequencer set to 'Any' immediately after the truncate job
Note the
non-use of enclosing
# for the parameter name here. If you've used the...err, 'long' versions noted above you could use the substring operator to check TruncateTable[1,1]="N" or "O". Of course, there is a link between the truncate Job Activity stage and the Sequencer stage.
After the Sequencer stage would come the jobs that you were normally running. Setup properly, this allows you to typically skip the truncate job (or jobs) at the head of the stream and dive right in just after that point. Occassionally, you switch the parameter to a 'Y' and it runs the truncate job(s) first in the stream.
Easy to setup, understand and maintain IMHO... much more so than magic sql buried away in before SQL tabs that most people will never know is there. Unless you are a firm believer in Annotating your jobs, that is - which you definitely should be.
My two cents.