May
10
2008

Adding the error column name to an error output

It’s a common frustration with Error Outputs in Integration Services that you get numeric identifiers for the error that occurred and the column that caused it, but you don’t automatically get the description of the error or the name of the column.

It’s easy to add the description of the error with 1 line of code in a Script component, as described in this BOL topic: Enhancing an Error Output with the Script Component.

It’s not so easy to add the name of the column.

(Note: In some cases, ErrorColumn = 0 when the error is deemed to affect the whole row and not a single column – for example, in the case of a failed Lookup.)

I’ve uploaded a sample that demonstrates a rather simplistic and “brute force”, but effective, approach: Create a reference table of Column IDs ahead of time (by running a small standalone application for this purpose), then simply perform a Lookup inside the package to add ErrorColumnName to the error output in the data flow.

Column IDs are unique within the containing Data Flow task, so your reference table requires at least these columns:

  • PackageID
  • DataFlowTaskID
  • ColumnID
  • ColumnName

The package uses the following components to add the 2 descriptive columns to the data flow. There are a few ways that you could rearrange or consolidate this, but I chose to make each step explicit.

  • Script component to add the Error Description. Connect this component to the Error Output. Add the new ErrorDescription output column and populate it with a line of code that calls:
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
  • Derived Column to add the system variables for PackageID and TaskID (the containing Data Flow task) to the data flow, for use in the Lookup of the column name.
  • Derived Column to add the new ErrorColumnName output column. Where ErrorColumn = 0, populate the ErrorColumnName column with some informative string like “[Not a column-specific error.]”
  • Conditional Split to split row-level errors from column-level errors. Check for ErrorColumn = 0 and send those rows out a separate output.
  • Lookup on the column-error output to look up the column name in the reference table by using PackageID, DataFlowTaskID, and ErrorColumn as parameters.
  • Union All to recombine the row-error and column-error paths

.


And finally, you have your error description and error column name, as seen here in the data viewer!

Comments (4) -

tnafoo

hi doug,


does the standalone application work for data flow tasks within a container?


it seems to have return nothing for my package that consists of containers.


-tnafoo


dougbert

tnafoo, I think that it will NOT, as written. You would need to tweak the following section of code to make it recursive; that is, to check the Executables collection of top-level Executables in the package to find nested Data Flow tasks. It seems that each layer only sees its immediate children.


   For Each pkgExecutable In currentPackage.Executables


I don't have time at the moment to rewrite this. For your purposes, it's probably good enough to hard-code the check of the next level down for now.


-Doug


Jos

Hi Doug


I've used your VB.NET code and it works just fine, except it doesn't perform at all. Ik takes a very long time to execute the statement "currentPackage = ssisApp.LoadPackage(package, Nothing)" It takes minutes and minutes to execute this statement???? Am I doing something wrong??


-Jos


dougbert

Jos, I assume that the delay is package validation occurring. Hard-to-reach connections can cause long delays. You should see similar timing for a similar reason if you open the same package in BIDS on the same computer. If you're convinced that this is not the case, then I recommend reposting your question to a larger audience in the SSIS forum at social.msdn.microsoft.com/.../">social.msdn.microsoft.com/.../threads, where a lot of SSIS developers will see it. Best wishes,   -Doug


Pingbacks and trackbacks (3)+