September 28, 2018 at 2:27 am
Hi folks,
I'm looking at needing to actively log and be able to present (probably via some sort of Dashboard Notification) SSIS Error Contents. For the Moment most interesting will be Flat file Rows that are supposed to be imported but fail due to whatever constraint. Right now most notably would be some Int Column receives some String because someone entered the wrong data somewhere else. Generally we want to have a centralized place so co-workers who have understanding about the data that failed, can review the data and take correctional measures. Has anyone approached something like this before and could share valuable insight / lessons learned from something like this?
General question while I'm on it: Can I somehow determine on which column the import of row X has failed? It would immensely make life easier for co-workers if they knew the last X rows failed to Import (and have been redirected to Error Output) because something is wrong with Column Y.
September 28, 2018 at 5:54 am
DinoRS - Friday, September 28, 2018 2:27 AMHi folks,
I'm looking at needing to actively log and be able to present (probably via some sort of Dashboard Notification) SSIS Error Contents. For the Moment most interesting will be Flat file Rows that are supposed to be imported but fail due to whatever constraint. Right now most notably would be some Int Column receives some String because someone entered the wrong data somewhere else. Generally we want to have a centralized place so co-workers who have understanding about the data that failed, can review the data and take correctional measures. Has anyone approached something like this before and could share valuable insight / lessons learned from something like this?General question while I'm on it: Can I somehow determine on which column the import of row X has failed? It would immensely make life easier for co-workers if they knew the last X rows failed to Import (and have been redirected to Error Output) because something is wrong with Column Y.
Which version of SSIS are you using? This became possible only from 2016 onwards. You'll need a Script Component located after your Error redirect, with some code looking a bit like this: public override void Input0_ProcessInputRow(Input0Buffer Row)
{
IDTSComponentMetaData130 cMD = ComponentMetaData as IDTSComponentMetaData130;
Row.ErrorDescription = cMD.GetErrorDescription(Row.ErrorCode);
Row.ErrorColumnName = cMD.GetIdentificationStringByID(Row.ErrorColumn);
}
Where ErrorDescription and ErrorColumnName have been defined as output columns for the script component.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 28, 2018 at 6:34 am
Hi Phil,
interesting. We're currently still on 2014 but I am looking forward to get to 2017 (and later 2019) rather earlier than later. But this specific functionality is something I just wished I had yesterday instead of enabling / disabling redirection column by column to find out why exactly the rows failed. I will keep that Script Component definitely for the future.
Any more ideas about the general SSIS logging DB thing asides how to configure log providers? I simply dislike reinventing the wheel over and over again. It was invented once, it was round and it still is. 😉
September 28, 2018 at 7:03 am
DinoRS - Friday, September 28, 2018 6:34 AMHi Phil,
interesting. We're currently still on 2014 but I am looking forward to get to 2017 (and later 2019) rather earlier than later. But this specific functionality is something I just wished I had yesterday instead of enabling / disabling redirection column by column to find out why exactly the rows failed. I will keep that Script Component definitely for the future.Any more ideas about the general SSIS logging DB thing asides how to configure log providers? I simply dislike reinventing the wheel over and over again. It was invented once, it was round and it still is. 😉
Before 2016, the only other option I can think of is a 3rd party add-on (link) which has an 'Error Output Description' component.
There is no 'SSIS Logging DB" – SSISDB includes logging as one of its functions, but has many others.
Regarding general logging ideas: I started writing something and then realised it's rather a large topic to cover in a thread answer here. Some general ideas:
1) Avoid copy/paste of error handling code wherever you can. Instead, handle errors at the outermost level you can get away with.
2) When loading data, collect as many errors as you can before failing the process.
(This gives people the chance to fix all the errors in one sweep, rather than drip-feeding the errors out.)
I do this by updating a Boolean Failed marker variable to 1 in the case of 1 or more errors and then checking its value at the end to determine overall run status.
3) Don't log to files.
Why would you? You've got SQL Server.
4) Don't log warning messages which no one ever looks at.
So tempting to do this as a developer, but all you end up doing is unnecessarily using disk space and creating spam.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 28, 2018 at 10:05 am
DinoRS - Friday, September 28, 2018 2:27 AMHi folks,
I'm looking at needing to actively log and be able to present (probably via some sort of Dashboard Notification) SSIS Error Contents. For the Moment most interesting will be Flat file Rows that are supposed to be imported but fail due to whatever constraint. Right now most notably would be some Int Column receives some String because someone entered the wrong data somewhere else. Generally we want to have a centralized place so co-workers who have understanding about the data that failed, can review the data and take correctional measures. Has anyone approached something like this before and could share valuable insight / lessons learned from something like this?General question while I'm on it: Can I somehow determine on which column the import of row X has failed? It would immensely make life easier for co-workers if they knew the last X rows failed to Import (and have been redirected to Error Output) because something is wrong with Column Y.
Another way to skin this kind of cat is to treat the data not unlike the way you might import flat-file data into a data warehouse... You can import the flat file as an entire row to a staging table with zero edits. It's a never fail scenario. Then you parse the data in SSIS and redirect any row that won't fully parse into any other output than the normal one. Then your parsing can determine which column or columns are a problem. Admittedly, it''s a lot more work, so doing it should generally be something where the importance of the data as well as the expectations for that data, justifies the effort.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply