Saturday, February 25, 2012

Add Extra Error Columns - From The Component?

I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.

I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.

Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.

This might be possible. Try the following (I'm assuming your custom component is synchronous):

1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.

2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).

3. If you got the handle, use it in ProcessInput to set the error description before calling RedirectErrorRow.

|||

Ted Lee - MSFT wrote:

This might be possible. Try the following (I'm assuming your custom component is synchronous):

1. In your implementation of ProvideComponentProperties, create 2 outputs that are synchronous to the same input, put them in the same exclusion group and mark one of them as the error output. By marking it as an error output, you'll automatically get the 2 error output columns and the output should show up red in the designer. Then add a third column to that output for your error description.

That part worked. The extra column was added to the error output. Unfortunately:

Ted Lee - MSFT wrote:

2. In PreExecute, use FindColumnByLineageID to get the handle to the error description column you added. If you can't get the handle, then this isn't possible (I haven't tried it, but I can't think of anything in the API to keep you from getting it).

The above caused an error during PreExecute:

The output "TranslateProvidersErrorOutput" (347) is a synchronous output and the buffer type cannot be retrieved for a synchronous output.

The code I tried is:

Code Snippet

public override void ProvideComponentProperties()

{

ComponentMetaData.UsesDispositions = true;

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "TranslateProvidersInput";

input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "TranslateProvidersOutput";

output.SynchronousInputID = input.ID;

output.ExclusionGroup = 1;

// The new code:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();

errorOutput.Name = "TranslateProvidersErrorOutput";

errorOutput.SynchronousInputID = input.ID;

errorOutput.ExclusionGroup = 1;

errorOutput.IsErrorOut = true;

IDTSOutputColumn90 extraColumn = errorOutput.OutputColumnCollection.New();

extraColumn.Name = "ErrorDescription";

extraColumn.SetDataTypeProperties(DataType.DT_WSTR, 255, 0, 0, 0);

_extraColumnLineageID = extraColumn.LineageID;

// The original code:

//AddErrorOutput(

//"TranslateProvidersErrorOutput", input.ID, output.ExclusionGroup);

IDTSRuntimeConnection90 connection =

ComponentMetaData.RuntimeConnectionCollection.New();

connection.Name = "ADONET";

}

public override void PreExecute()

{

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

}

The hightlighted code is what failed.

I don't yet understand how buffers work with multiple outputs, so I can't figure out any other way to get the column index. I'm stuck at that point, for now.

If anyone has more ideas, I'll be glad to try them. One workaround I thought of was to add the extra columns to the default output and see if they get replicated to the error output when the default error output is created. This would have the unfortunate side-effect of proliferating error-only columns throughout the data flow, but should work well enough if the extra columns are truly required.

It will be necessary for components in the flow to not add one of these columns if it is already present.

|||

John,

I've got this working in my destination component: adding error messages, row #, time, etc.

The difference I think is in this line:

IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection[1];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

errorOutput.Buffer, _extraColumnLineageID);

The equivalent in my code would be:

IDTSInput90 input = ComponentMetaData.InputCollection[0];

_extraColumnIndex =

BufferManager.FindColumnByLineageID(

input.Buffer, _extraColumnLineageID);

It is the input buffer that actually has the slots for the new error columns.

Martin

No comments:

Post a Comment