When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?
I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;
// Add extra column here, e.g. ErrorMessage
Answering my own question. The answer is 'YES', you can enhance the error output.
You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.
===============================================================================
// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);
===============================================================================
// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;
// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);
===============================================================================
// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion
buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}
===============================================================================
|||
Can you give me more details on this. I will wait to see if you reply beofre I elaborate..
It sounds like this is something I am looking to do based on my post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1
|||
Rob,
The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.
-Jamie
|||
Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?
|||
 ronemac wrote: |
|
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented? |
If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.
-Jamie
Matt David wrote:
)