February 28, 2017 at 5:27 am
How to retrieve Error column name in SSIS 2012
I am having 90 columns how to find for which column what is the error
there might be possible to have more than 2 errors in single record, how to know for which record what is the column name and what is error.
there are millions of records will be coming in a day.in SSIS 2016
public override void Input0_ProcessInputRow(Input0Buffer Row){
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}Thanks
February 28, 2017 at 5:48 am
How to retrieve Error column name in SSIS 2012I am having 90 columns how to find for which column what is the error
there might be possible to have more than 2 errors in single record, how to know for which record what is the column name and what is error.
there are millions of records will be coming in a day.in SSIS 2016
public override void Input0_ProcessInputRow(Input0Buffer Row){
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}
And that's one of the big reasons 2016 is a worthwhile upgrade. From memory, when I investigated this, the best way of getting what you wanted was via a third-party add-on (here's one that I tested – the ERROR OUTPUT DESCRIPTION TRANSFORM). There is no easy way of getting this info natively.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 9:10 am
Look into the OnError Event Handler. It's where you can get the exact error message in a variable that you can then put into an audit table, email, etc.
February 28, 2017 at 9:24 am
JustMarie - Tuesday, February 28, 2017 9:10 AMLook into the OnError Event Handler. It's where you can get the exact error message in a variable that you can then put into an audit table, email, etc.
But I don't think it tells you which column caused the error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 9:35 am
Phil Parkin - Tuesday, February 28, 2017 9:24 AMJustMarie - Tuesday, February 28, 2017 9:10 AMLook into the OnError Event Handler. It's where you can get the exact error message in a variable that you can then put into an audit table, email, etc.But I don't think it tells you which column caused the error.
The system variable System::ErrorDescription (which you can only get to in the OnError Event Handler) does give a full and descriptive error message including fields. At least it does for all the various types of errors my packages catch.
February 28, 2017 at 9:51 am
JustMarie - Tuesday, February 28, 2017 9:35 AMPhil Parkin - Tuesday, February 28, 2017 9:24 AMJustMarie - Tuesday, February 28, 2017 9:10 AMLook into the OnError Event Handler. It's where you can get the exact error message in a variable that you can then put into an audit table, email, etc.But I don't think it tells you which column caused the error.
The system variable System::ErrorDescription (which you can only get to in the OnError Event Handler) does give a full and descriptive error message including fields. At least it does for all the various types of errors my packages catch.
Well, that is interesting and I wish I'd known about it.
So are you able to use this in the case where your input file contains millions of rows and you want to record all the errors in it, or does execution bomb out when an error occurs?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 12:08 pm
Phil Parkin - Tuesday, February 28, 2017 9:51 AMJustMarie - Tuesday, February 28, 2017 9:35 AMPhil Parkin - Tuesday, February 28, 2017 9:24 AMJustMarie - Tuesday, February 28, 2017 9:10 AMLook into the OnError Event Handler. It's where you can get the exact error message in a variable that you can then put into an audit table, email, etc.But I don't think it tells you which column caused the error.
The system variable System::ErrorDescription (which you can only get to in the OnError Event Handler) does give a full and descriptive error message including fields. At least it does for all the various types of errors my packages catch.
Well, that is interesting and I wish I'd known about it.
So are you able to use this in the case where your input file contains millions of rows and you want to record all the errors in it, or does execution bomb out when an error occurs?
There's another system variable in the OnError called Propagate that keeps the process running even if it finds errors. I'm not sure how that works with a data flow (interesting thing to test) but it's worth a shot to keep things running even when it finds errors.
Is there a reason you wouldn't use transformations with On Failure criteria to filter those rows into an error table? And then send an email that X number of errors were found, pointing back to the audit/error log with the details?
February 28, 2017 at 12:14 pm
JustMarie - Tuesday, February 28, 2017 12:08 PMThere's another system variable in the OnError called Propagate that keeps the process running even if it finds errors. I'm not sure how that works with a data flow (interesting thing to test) but it's worth a shot to keep things running even when it finds errors.
Is there a reason you wouldn't use transformations with On Failure criteria to filter those rows into an error table? And then send an email that X number of errors were found, pointing back to the audit/error log with the details?
I've been using 2016 since it was released and older mechanisms are fading from my memory rapidly! But I find it easiest to redirect all errors to an error table and then set up a later package which reads the errors & decides whether to terminate, or carry on with warnings.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply