question

jmelb79 avatar image
jmelb79 asked

Adding Audit Columns to Redirected Error Rows of Data Flow Task in SSIS

Hi,

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!

ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered

Edit: I wanted to answer you quickly before, but I didn't have time for a complete answer. I will elaborate.

Part 1:
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.

Part 2:
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:

  1. Create a new MaxErrorRowsCount package variable.
  2. Add the script tranformation to the error path with the following code: (I had to manually format with HTML to get it to display correctly.)

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
    Dim MyRowCount As Integer = 0
    While Buffer.NextRow()
        'Input0_ProcessInputRow(Buffer)
        MyRowCount = MyRowCount + 1
        If MyRowCount > Variables.MaxErrorRowsCount Then
            Throw New Exception()
        End If
    End While
End Sub

  1. Put your original error destination after the script.

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.