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!
Answer by Tom Staab ·
Edit: I wanted to answer you quickly before, but I didn't have time for a complete answer. I will elaborate.
You can insert a Derived Columns transformation in your error path before your error destination. Then add new columns from user and system variables or other expressions. Finally, map the new column(s) in your error destination.
Your suspicion is correct. The MaximumErrorCount property is in reference to container errors. If you redirect row errors in a data flow, you have successfully handled them so the container (the data flow) finishes successfully. Consider the property to mean Maximum**Unhandled**ErrorCount. You can insert a Row Count transformation to count how many rows are redirected. If you don't want to log the errors if the count exceeds a limit, this is what I suggest:
Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
Dim MyRowCount As Integer = 0
MyRowCount = MyRowCount + 1
If MyRowCount > Variables.MaxErrorRowsCount Then
Throw New Exception()
If there are too many errors, the task (and package) will fail. If not too many, they will be logged. I tested it, and it worked for me.