I've got a simple data flow task where I import a source file into a destination table on a SQL server db. Any rows that error I redirect to another table for auditing. This works fine. However, for the error rows, I want to be able to include extra audit columns such as RunId (each run of my SSIS package gets a RunId so this allows me to distinguish different runs and link the error row with the run it came from) and CreateDate. By default there are only 3 output error columns 'Flat File Source Error Output Column' (contains the error record), 'ErrorCode' and 'ErrorColumn'. I have tried adding the extra columns I need in the error output using the 'Input and Output Properties' tab of the Advanced Editor for the source file but this gives me the error "The component does not allow adding columns to this input or output". Does anyone know a way for me to do this?
Also, I have the suspicion that because I am redirecting error records the 'MaximumErrorCount' property of the data flow task will not cause the data flow to fail even if the number of error records redirected exceeds the value of this property. The files I am importing are massive (in excess of 5GB with up to 4million recs) and I want to make sure that the package fails if there is a large number of failures without having to log every single failure. Is my suspicion correct and if so how can i get around this?
Thanks for any help!