problem with file handling

[ds_team]

I am executing a stored procedure using ODBC stage and i am writing the the
output to a file using sequential file stage. In between ODBC stage and sequential file stage i have
transformer stage.
My job runs daily. If suppose my stored procedure returned 5 rows for todays run then that
data gets written to sequential file.
But for next days run if i dont have any output for
stored procedure my job should not write anything to the sequential file and should keep the previous days data intact.
But in the case where there is no output it removes the content of the file. How can i stop this?

[chulett]

Setting the stage to 'Append' is the only way to keep zero records from 'removing the contents'. However, a 'normal' run won't do so either. Perhaps you should use a date in the filename so that each day you get a new file?

[gateleys]

You could use the following jobs to achieve this -

Code: Select all

JOB1

Xfmr ------> TargetSequentialFile (Just to create the file)

Next,

Code: Select all

JOB2

ODBC (StoredProc) -----> Xfmr ------> TempSequentialFile

Then,

Code: Select all

JOB3

TempSequentialFile -----> Xfmr ------> TargetSequentialFile

Now, at the sequence level (after JOB2), use the logic -

Code: Select all

If size_of_TempSequentialFile > 0 Then
    fire JOB3
Else
   continue without executing JOB3

NOTE: You need to use TargetSequentialFile in your subsequent jobs and not TempSequentialFile.
Use the STATUS statement to check filesize.

gateleys