Using Error Outputs in a Data Flow Component
Special IDTSOutput90 objects called error outputs can be added to components to let the component redirect rows that it cannot process during execution. The problems a component may encounter are generally categorized as errors or truncations, and are specific to each component. Components that provide error outputs give users of the component the flexibility to handle error conditions by filtering error rows out of the result set, by failing the component when a problem occurs, or by ignoring errors and continuing.
To implement and support error outputs in a component, you must first set the UsesDispositions property of the component to true. Then you must add an output to the component that has its IsErrorOut property set to true. Finally, the component must contain code that redirects rows to the error output when errors or truncations occur. This topic covers these three steps and explains the differences between synchronous and asynchronous error outputs.
Creating an Error Output
You create an error output by calling the New method of the OutputCollection, and then setting the IsErrorOut property of the new output to true. If the output is asynchronous, nothing else must be done to the output. If the output is synchronous, and there is another output that is synchronous to the same input, you must also set the ExclusionGroup and SynchronousInputID properties. Both properties should have the same values as the other output that is synchronous to the same input. If these properties are not set to a non-zero value, the rows provided by the input are sent to both outputs that are synchronous to the input.
When a component encounters an error or truncation during execution, it proceeds based on the settings of the ErrorRowDisposition and TruncationRowDisposition properties of the input or output, or input or output column, where the error occurred. The value of these properties should be set by default to RD_NotUsed. When the error output of the component is connected to a downstream component, this property is set by the user of the component and lets the user control how the component handles the error or truncation.
Populating Error Columns
When an error output is created, the data flow task automatically adds two columns to the output column collection. These columns are used by components to specify the ID of the column that caused the error or truncation, and to provide the component-specific error code. These columns are generated automatically, but the values contained in the columns must be set by the component.
The method used to set the values of these columns depends on whether the error output is synchronous or asynchronous. Components with synchronous outputs call the DirectErrorRow method, discussed in more detail in the next section, and provide the error code and error column values as parameters. Components with asynchronous outputs have two choices for setting the values of these columns. They can either call the SetErrorInfo method of the output buffer and supply the values, or locate the error columns in the buffer by using FindColumnByLineageID and set the values for the columns directly. However, because the names of the columns may have been changed, or their location in the output column collection may have been modified, the latter method may not be reliable. The SetErrorInfo method automatically sets the values in these error columns without having to locate them manually.
If you need to obtain the error description that corresponds to a specific error code, you can use the GetErrorDescription method of the IDTSComponentMetaData90 interface, available through the component's ComponentMetaData property.
The following code examples show a component that has an input and two outputs, including an error output. The first example shows how to create an error output that is synchronous to the input. The second example shows how to create an error output that is asynchronous.
public override void ProvideComponentProperties()
{
// Specify that the component has an error output.
ComponentMetaData.UsesDispositions = true;
// Create the input.
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
input.ErrorOrTruncationOperation = "A string describing the possible error or truncation that may occur during execution.";
// Create the default output.
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = input.ID;
errorOutput.ExclusionGroup = 1;
}
Public Overrides Sub ProvideComponentProperties()
' Specify that the component has an error output.
ComponentMetaData.UsesDispositions = True
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.New
' Create the input.
input.Name = "Input"
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
input.ErrorOrTruncationOperation = "A string describing the possible error or truncation that may occur during execution."
' Create the default output.
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New
output.Name = "Output"
output.SynchronousInputID = input.ID
output.ExclusionGroup = 1
' Create the error output.
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection.New
errorOutput.IsErrorOut = True
errorOutput.Name = "ErrorOutput"
errorOutput.SynchronousInputID = input.ID
errorOutput.ExclusionGroup = 1
End Sub
The following code example creates an error output that is asynchronous.
public override void ProvideComponentProperties()
{
// Specify that the component has an error output.
ComponentMetaData.UsesDispositions = true;
// Create the input.
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
input.ErrorOrTruncationOperation = "A string describing the possible error or truncation that may occur during execution.";
// Create the default output.
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.Name = "ErrorOutput";
errorOutput.IsErrorOut = true;
}
Public Overrides Sub ProvideComponentProperties()
' Specify that the component has an error output.
ComponentMetaData.UsesDispositions = True
' Create the input.
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.New
' Create the default output.
input.Name = "Input"
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
input.ErrorOrTruncationOperation = "A string describing the possible error or truncation that may occur during execution."
' Create the error output.
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New
output.Name = "Output"
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection.New
errorOutput.Name = "ErrorOutput"
errorOutput.IsErrorOut = True
End Sub
Redirecting a Row to an Error Output
After adding an error output to a component, you must provide code that handles the error or truncation conditions specific to the component and redirects the error or truncation rows to the error output. You can do this in two ways, depending on whether the error output is synchronous or asynchronous.
Redirecting a Row with Synchronous Outputs
Rows are sent to synchronous outputs by calling the DirectErrorRow method of the PipelineBuffer class. The method call includes as parameters the ID of the error output, the component-defined error code, and the index of the column that the component was unable to process.
The following code example demonstrates how to direct a row in a buffer to a synchronous error output using the DirectErrorRow method.
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (!buffer.EndOfRowset)
{
IDTSInput90 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
// This code sample assumes the component has two outputs, one the default,
// the other the error output. If the errorOutputIndex returned from GetErrorOutputInfo
// is 0, then the default output is the second output in the collection.
int defaultOutputID = -1;
int errorOutputID = -1;
int errorOutputIndex = -1;
GetErrorOutputInfo(ref errorOutputID,ref errorOutputIndex);
if (errorOutputIndex == 0)
defaultOutputID = ComponentMetaData.OutputCollection[1].ID;
else
defaultOutputID = ComponentMetaData.OutputCollection[0].ID;
while (buffer.NextRow())
{
try
{
// TODO: Implement code to process the columns in the buffer row.
// Ideally, your code should detect potential exceptions before they occur, rather
// than having a generic try/catch block such as this.
// However, because the error or truncation implementation is specific to each component,
// this sample focuses on actually directing the row, and not a single error or truncation.
// Unless an exception occurs, direct the row to the default
buffer.DirectRow(defaultOutputID);
}
catch
{
// Yes, has the user specified to redirect the row?
if (input.ErrorRowDisposition == DTSRowDisposition.RD_RedirectRow)
{
// Yes, direct the row to the error output.
// TODO: Add code to include the errorColumnIndex.
buffer.DirectErrorRow(errorOutputID, 0, errorColumnIndex);
}
else if (input.ErrorRowDisposition == DTSRowDisposition.RD_FailComponent || input.ErrorRowDisposition == DTSRowDisposition.RD_NotUsed)
{
// No, the user specified to fail the component, or the error row disposition was not set.
throw new Exception("An error occurred, and the DTSRowDisposition is either not set, or is set to fail component.");
}
else
{
// No, the user specified to ignore the failure so
// direct the row to the default output.
buffer.DirectRow(defaultOutputID);
}
}
}
}
}
Public Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Not buffer.EndOfRowset Then
Dim input As IDTSInput90 = ComponentMetaData.InputCollection.GetObjectByID(inputID)
' This code sample assumes the component has two outputs, one the default,
' the other the error output. If the errorOutputIndex returned from GetErrorOutputInfo
' is 0, then the default output is the second output in the collection.
Dim defaultOutputID As Integer = -1
Dim errorOutputID As Integer = -1
Dim errorOutputIndex As Integer = -1
GetErrorOutputInfo(errorOutputID, errorOutputIndex)
If errorOutputIndex = 0 Then
defaultOutputID = ComponentMetaData.OutputCollection(1).ID
Else
defaultOutputID = ComponentMetaData.OutputCollection(0).ID
End If
While buffer.NextRow
Try
' TODO: Implement code to process the columns in the buffer row.
' Ideally, your code should detect potential exceptions before they occur, rather
' than having a generic try/catch block such as this.
' However, because the error or truncation implementation is specific to each component,
' this sample focuses on actually directing the row, and not a single error or truncation.
' Unless an exception occurs, direct the row to the default
buffer.DirectRow(defaultOutputID)
Catch
' Yes, has the user specified to redirect the row?
If input.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow Then
' Yes, direct the row to the error output.
' TODO: Add code to include the errorColumnIndex.
buffer.DirectErrorRow(errorOutputID, 0, errorColumnIndex)
Else
If input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent OrElse input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed Then
' No, the user specified to fail the component, or the error row disposition was not set.
Throw New Exception("An error occurred, and the DTSRowDisposition is either not set, or is set to fail component.")
Else
' No, the user specified to ignore the failure so
' direct the row to the default output.
buffer.DirectRow(defaultOutputID)
End If
End If
End Try
End While
End If
End Sub
Redirecting a Row with Asynchronous Outputs
Instead of directing rows to an output, as is done with synchronous error outputs, components with asynchronous outputs send a row to an error output by explicitly adding a row to the output PipelineBuffer. Implementing a component that uses asynchronous error outputs requires adding columns to the error output that are provided to downstream components, and caching the output buffer for the error output that is provided to the component during the PrimeOutput method. The details of implementing a component with asynchronous outputs are covered in detail in the topic Developing a Custom Transformation Component with Asynchronous Outputs. If columns are not explicitly added to the error output, the buffer row that is added to the output buffer contains only the two error columns.
To send a row to an asynchronous error output, you must add a row to the error output buffer. Sometimes, a row may have already been added to the non-error output buffer and you must remove this row by using the RemoveRow method. Next you set the output buffer columns values, and finally, you call the SetErrorInfo method to provide the component-specific error code and the error column value.
The following example demonstrates how to use an error output for a component with asynchronous outputs. When the simulated error occurs, the component adds a row to the error output buffer, copies the values that were previously added to the non-error output buffer to the error output buffer, removes the row that was added to the non-error output buffer, and, finally, sets the error code and error column values by calling the SetErrorInfo method.
int []columnIndex;
int errorOutputID = -1;
int errorOutputIndex = -1;
public override void PreExecute()
{
IDTSOutput90 defaultOutput = null;
this.GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);
foreach (IDTSOutput90 output in ComponentMetaData.OutputCollection)
{
if (output.ID != errorOutputID)
defaultOutput = output;
}
columnIndex = new int[defaultOutput.OutputColumnCollection.Count];
for(int col =0 ; col < defaultOutput.OutputColumnCollection.Count; col++)
{
IDTSOutputColumn90 column = defaultOutput.OutputColumnCollection[col];
columnIndex[col] = BufferManager.FindColumnByLineageID(defaultOutput.Buffer, column.LineageID);
}
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
for( int x=0; x < outputs; x++ )
{
if (outputIDs[x] == errorOutputID)
this.errorBuffer = buffers[x];
else
this.defaultBuffer = buffers[x];
}
int rows = 100;
Random random = new Random(System.DateTime.Now.Millisecond);
for (int row = 0; row < rows; row++)
{
try
{
defaultBuffer.AddRow();
for (int x = 0; x < columnIndex.Length; x++)
defaultBuffer[columnIndex[x]] = random.Next();
// Simulate an error.
if ((row % 2) == 0)
throw new Exception("A simulated error.");
}
catch
{
// Add a row to the error buffer.
errorBuffer.AddRow();
// Get the values from the default buffer
// and copy them to the error buffer.
for (int x = 0; x < columnIndex.Length; x++)
errorBuffer[columnIndex[x]] = defaultBuffer[columnIndex[x]];
// Set the error information.
errorBuffer.SetErrorInfo(errorOutputID, 1, 0);
// Remove the row that was added to the default buffer.
defaultBuffer.RemoveRow();
}
}
if (defaultBuffer != null)
defaultBuffer.SetEndOfRowset();
if (errorBuffer != null)
errorBuffer.SetEndOfRowset();
}
Private columnIndex As Integer()
Private errorOutputID As Integer = -1
Private errorOutputIndex As Integer = -1
Public Overrides Sub PreExecute()
Dim defaultOutput As IDTSOutput90 = Nothing
Me.GetErrorOutputInfo(errorOutputID, errorOutputIndex)
For Each output As IDTSOutput90 In ComponentMetaData.OutputCollection
If Not (output.ID = errorOutputID) Then
defaultOutput = output
End If
Next
columnIndex = New Integer(defaultOutput.OutputColumnCollection.Count) {}
Dim col As Integer = 0
While col < defaultOutput.OutputColumnCollection.Count
Dim column As IDTSOutputColumn90 = defaultOutput.OutputColumnCollection(col)
columnIndex(col) = BufferManager.FindColumnByLineageID(defaultOutput.Buffer, column.LineageID)
System.Math.Min(System.Threading.Interlocked.Increment(col),col-1)
End While
End Sub
Public Overrides Sub PrimeOutput(ByVal outputs As Integer, ByVal outputIDs As Integer(), ByVal buffers As PipelineBuffer())
Dim x As Integer = 0
While x < outputs
If outputIDs(x) = errorOutputID Then
Me.errorBuffer = buffers(x)
Else
Me.defaultBuffer = buffers(x)
End If
System.Math.Min(System.Threading.Interlocked.Increment(x),x-1)
End While
Dim rows As Integer = 100
Dim random As Random = New Random(System.DateTime.Now.Millisecond)
Dim row As Integer = 0
While row < rows
Try
defaultBuffer.AddRow
Dim x As Integer = 0
While x < columnIndex.Length
defaultBuffer(columnIndex(x)) = random.Next
System.Math.Min(System.Threading.Interlocked.Increment(x),x-1)
End While
' Simulate an error.
If (row Mod 2) = 0 Then
Throw New Exception("A simulated error.")
End If
Catch
' Add a row to the error buffer.
errorBuffer.AddRow
' Get the values from the default buffer
' and copy them to the error buffer.
Dim x As Integer = 0
While x < columnIndex.Length
errorBuffer(columnIndex(x)) = defaultBuffer(columnIndex(x))
System.Math.Min(System.Threading.Interlocked.Increment(x),x-1)
End While
' Set the error information.
errorBuffer.SetErrorInfo(errorOutputID, 1, 0)
' Remove the row that was added to the default buffer.
defaultBuffer.RemoveRow
End Try
System.Math.Min(System.Threading.Interlocked.Increment(row),row-1)
End While
If Not (defaultBuffer Is Nothing) Then
defaultBuffer.SetEndOfRowset
End If
If Not (errorBuffer Is Nothing) Then
errorBuffer.SetEndOfRowset
End If
End Sub
See Also
Concepts
Using Error Outputs in a Data Flow Component
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|