SSIS: Writing to log files, but maintaining a single header.

So I had a small issue where I wanted to create a log file during the on error step of a precedence constraint.  For example, while dynamically pulling from a list of sites, one of the sites does not match the definition of a "valid" site, and the data flow component errors, but I don't want to error the entire package, and I only want to write to the log file IF there is an error with a site, but otherwise do NOT create a log file.

Well, I got this set up, and I had multiple steps writing to the error file, and I had the options set so that each time it writes it does not overwrite the file.

So I get a file that looks like:

Header
Record
Header
Record
Header
Record

But, I only want to write the header one time, instead of multiple times, but I have no way of controlling with the SSIS package is going to throw the errors.  The users may have "fixed" the site I was trying to fix.

So, after a bit of thinking, the solution I came up with seems to work pretty well.

I created an overall variable to track whether or not I have written an error or not, called User::HasWrittenError, and then set it initially to false.  In the step that writes the errors out, I put a script transform task in the data flow, and in the post execute of the data flow script component, I added the HasWrittenError to my script.



In the script task itself, in the post execute, I did the following:

public override void PostExecute()
    {
        base.PostExecute();

        Variables.HasWrittenError = true;
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

So now that the code sets the HasWrittenError, I added this to each of the logging file steps.

In the file connection manager for the output, I added an expression to set the ColumNamesInFirstDataRow option to @[User::HasWrittenError] != TRUE.



What this allows is that after the first execution, when HasWritten error is "false" it will write the header on the first run, and also create the file, on the second execution through, HasWrittenError will be "true" and thus no header will be written to the file.