I have created a SSIS custom data flow component that performs the simple task of converting dates from a COBOL mainframe date types, that are in the format of CYYMMDD, to a SQL Server supported format of YYYYMMDD. There are error rows because the incoming COBOL formatted dates can be invalid dates (i.e., 2-29-2015, 4-31-2016, 9-31-2010, etc). These bad dates are from user derived input fields that do not have a date mask on them and I cannot add a date mask because the application belongs to a 3rd party data vendor.
My problem:
The custom component will not re-direct error rows. I found a posting on MSDN that explains how to re-direct error rows:
https://msdn.microsoft.com/en-us/library/ms136009.aspx
I used this code as a guide and modified it to suit my needs of being able to execute over multiple selected input columns (MS's example only contemplates one input column). When I execute the process, I get the following two errors:
[ConvertCobolDates [2]] Error: System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.DirectErrorRow(Int32 hRow, Int32 lOutputID, Int32 lErrorCode, Int32 lErrorColumn) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.DirectErrorRow(Int32 outputID, Int32 errorCode, Int32 errorColumn) at SSIS.Convert.CobolDate.DataFlow.ConvertCobolDateDataFlow.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket) [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ConvertCobolDates" (2) failed with error code 0x80070057 while processing input "Input" (4). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Also, I don't know if I am missing code to specifically redirect the output or if the error handling is being done incorrectly - it does not show up in the Configure Error Output screen. Any assistance is greatly appreciated! enter image description here
Note: I suspect the error is in one of the following places: ProvideComponentProperties or ProcessInput.
public override void ProvideComponentProperties()
{
try
{
// Perform the base class' method
base.ProvideComponentProperties();
// Start out clean, remove anything put on by the base class
base.RemoveAllInputsOutputsAndCustomProperties();
// Set component information
ComponentMetaData.Name = "ConvertCobolDates";
ComponentMetaData.Description = "Data Flow task that converts COBOL date types into SQL Server Date types for each row flowing through the component.";
ComponentMetaData.ContactInfo = "Contact Info.";
ComponentMetaData.UsesDispositions = true; // As a rule, components should support error dispositions - they make it easier to troubleshoot problems with the data
// Create input objects. This allows the custom component to have a 'Success' input data flow line
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
input.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow; // Use RD_RedirectRow is ComponentMetaData.UsesDispositions = true. Otherwise, use RD_NotUsed
input.ErrorOrTruncationOperation = "Either a bad date has been detected or an input column(s) has been selected that does not contain dates.";
// Create output objects. This allows the custom component to have a 'Success' output data flow line
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.SynchronousInputID = input.ID; //Synchronous transformation
output.ExclusionGroup = 1;
// Create output objects. This allows the custom component to have a 'Error' output data flow line
IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = input.ID;
errorOutput.ExclusionGroup = 1;
}
catch (Exception ex)
{
bool bolCancel = false;
ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, "", 0, out bolCancel);
throw;
}
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
// This code assumes the component has two outputs, one the default,
// the other the error output. If the intErrorOutputIndex returned from GetErrorOutputInfo
// is 0, then the default output is the second output in the collection.
int intDefaultOutputID = -1;
int intErrorOutputID = -1;
int intErrorOutputIndex = -1;
int intErrorColumnIndex = -1;
bool bolValidDate = false;
GetErrorOutputInfo(ref intErrorOutputID, ref intErrorOutputIndex);
if (intErrorOutputIndex == 0)
intDefaultOutputID = ComponentMetaData.OutputCollection[1].ID;
else
intDefaultOutputID = ComponentMetaData.OutputCollection[0].ID;
// Process each incoming row
while (buffer.NextRow())
{
try
{
for (int i = 0; i < inputBufferColumnIndex.Length; i++)
{
if (!buffer.IsNull(inputBufferColumnIndex[i]))
{
// Get the name of the current column that is being processed
string strColName = this.ComponentMetaData.InputCollection[0].InputColumnCollection[i].Name;
// Get the current row number that is being processed
int intCurRow = buffer.CurrentRow + 2; // Buffer.CurrentRow is zero bounded and the first row is a header row, which is skipped. Adjust by two to account for this
// 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.
// Get the ID of the PipelineBuffer column that may cause an error. This is required for redirecting error rows
intErrorColumnIndex = this.ComponentMetaData.InputCollection[0].InputColumnCollection[i].ID;
string strCobolDate = buffer.GetString(inputBufferColumnIndex[i]);
string strConvertedCobolDate = ConvertCobolDate(strCobolDate, strColName, intCurRow);
DateTime dtConvertedSQLDate;
// Validate that the date is correct. This detects bad dates (e.g., 2-30-2016, 4-31-2015, etc.) that are inputted from the user
// Throw an error if the date is bad
bolValidDate = DateTime.TryParse(strConvertedCobolDate, out dtConvertedSQLDate);
if (!bolValidDate)
{
// Validation failed, throw an exception and redirect the error row
throw new Exception();
}
else if (bolValidDate)
{
// validation passed. Direct the column back to its corresponding row within the pipeline buffer
buffer[inputBufferColumnIndex[i]] = dtConvertedSQLDate.ToShortDateString();
}
}
}
// Unless an exception occurs, direct the row to the default
buffer.DirectRow(intDefaultOutputID);
}
catch(Exception)
{
// Has the user specified to redirect the row?
if (input.ErrorRowDisposition == DTSRowDisposition.RD_RedirectRow)
{
// Yes, direct the row to the error output.
buffer.DirectErrorRow(intErrorOutputID, 0, intErrorColumnIndex);
}
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(intDefaultOutputID);
}
}
}
}
