Try this (untested and may require refinement)
private string tempestRef;
public override void PostExecute()
{
base.PostExecute();
}

public override void Input0_ProcessInputRow(Input0Buffer Row)

{
if (Row.TempestRef_IsNull)
{
Row.TempestRef =...

I think you would need to use a Script Component Transformation to do this.
Here is an example of something similar. Have a read and then post back...

We can't answer this, because we do not have access to your data.
What I can suggest is that you trim down your query so that it contains only SELECT...

With >500 points, you're clearly not a newcomer here and should know by now that you need to provide sample DDL + INSERT statements for test data + desired results,...

Alternatively, why not something like
IF TRY_CAST(Column1 as INT) is NULL
BEGIN
--Write to error log
END?
Though you would have to do this as part of the FETCH loop,...