Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

capturing Error Column Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 8:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 8:45 AM
Points: 253, Visits: 1,100
I have a error table to capture error record, error description and error column, while reading from a flat file source error output.

I'm unable to figure out to populate error column field. any ideas?.

Post #456102
Posted Thursday, February 14, 2008 10:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
Which column are you talking about? The source table or the destination?
Or the conversion error on the column?


M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Post #456144
Posted Tuesday, February 19, 2008 2:42 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
I have just finished doing something similar like you. From flat file source, grab the failure precedent constraint (red arrow) and put it in a script task transformation. Then configure the task and in script click design script and add this script there:

Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

Then to a destination of your choice!!!!!


Imagination is more important than knowledge-Albert Einstein
Post #457666
Posted Tuesday, February 19, 2008 5:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 8:45 AM
Points: 253, Visits: 1,100
your code captures - error description from error code. Is there any way we can capture - actual error causing column name from error column ID?.



Post #457730
Posted Tuesday, February 19, 2008 7:09 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Gosh I know that's gonna be hard. I thought you just wanted SSIS error code. Well, to do that, you have to customize some codes in the script component according to what you know the error would be. Let's say you have datatype mismatch, you will have to point that to the column in the script component itself. For this, again, you would need .NET program to help you.

Or, let say you want to just separate null values where it should not be null, you can do this in conditional split. The code should be something like this:
ISNULL(ColumnName1)&&ISNULL(ColumnName2) and so on......
Name this as NULLS
So you will have this error generated by conditional split. Then point this to a separate destination of your choice, lets say a table or flatfile. When you drag an arrow(green one), the one you named NULLS just now will now appear. take that to the destination. Now, this destination will have only nulls and the column names will also be there as you wished.

The negative side of doing this in a conditional split is that if you have 50 errors to generate, you will have to do it for 50 of them each. That can be frustrating and not professional. Otherwise, if you know the errors that you are about to predict and they are just about 4 or 5, then you can use this approach. But, the best is always by coding it into a script using any .NET codes.

Hope I help you a little here.........


Imagination is more important than knowledge-Albert Einstein
Post #457757
Posted Tuesday, February 19, 2008 8:23 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
Hey guys! Look here and see that we are discussing the same problem.

In brief -- the element where you want to grab column names is not the one where the columns are. The columns definition happens in the previous block, which is the source of the error information for the script.
So, there is no easy way you can do it. You are stuck with those dodgy IDs, which are visible only in the advanced UI editor.

To access the names from within the element which generated the error you do something like this:

CStr(ComponentMetaData.InputCollection.Item(0).InputColumnCollection(0).Name)


but unfortunately datasource block generates the error information by itself and spits out only ErrorCode and ColumnID.

Looks like we need to place switch or if-else block in the error processing script to determine the column names, and apparently hard-code the IDs in the script.

I really don't want to do it since I have thousands of fields in hundreds of tables.

Does anyone know a better way?


M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Post #457780
Posted Wednesday, February 20, 2008 2:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Not a guru but a thought, since you have the column ID (assuming it is the column with the error) use this column Id as a parameter in a T-SQL statement to select from sys.columns the object_id which (defined as "ID of the object to which this column belongs"), and name (defined as "the name of the column") then using this object_id do a select on sys.objects to return the object name. This should result in the table name and the column name.

Refer to BOL sys.objects and sys.columns

Hope this helps


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #458226
Posted Wednesday, February 20, 2008 3:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
bitbucket (2/20/2008)
Not a guru but a thought, since you have the column ID (assuming it is the column with the error) use this column Id as a parameter in a T-SQL statement to select from sys.columns the object_id which (defined as "ID of the object to which this column belongs"), and name (defined as "the name of the column") then using this object_id do a select on sys.objects to return the object name. This should result in the table name and the column name.

Refer to BOL sys.objects and sys.columns

Hope this helps


NAAA. Column ID is not the one you are talking about. This is an internal SSIS identifier of the column within a source-destination or transformation task. It is essentially something around 100, then 500 and then it grows.
M$ themselves admitted that this is a missing feature.

I am just wondering (VB/SSIS gurus please help us here) if there is a way to use SSIS API in VB and access the previous task and its fields and then lookup for the name using the provided ID??

If there is no way, then too bad... I will create hundreds SELECT.. with thousands of CASE.. statements to figure the field..
Or maybe I can create a lookup table for it? Which maps sys.columns to the SSIS IDs?//


M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Post #458263
Posted Thursday, February 21, 2008 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 1:20 PM
Points: 7, Visits: 72
First off, I want to say that I’m quite impressed by the power and flexibility of SSIS, I just wished there was a better way to track data errors and to know which row and column caused the error.

To perform this type of error logging, I was using the Derived Column task to add addition metadata (i.e. Package Name, Task Name, Date and a custom error message to help identify which row caused the error), the Script Component task to get the error message and then a Destination task to store the data about the error. I added these three additional tasks to every major step in a Data Flow. This usually meant that I added on average 30 additional tasks per Data Flow (3 error logging tasks per 10 main steps).

This method of reporting data errors works fine, but it is very tedious to develop and maintain. The three additional tasks are identical for every main task that error logging is needed. Also, every time the metadata changed, the ETL Developer will need to refresh the metadata of all the additional tasks. Another shortcoming of this method of logging errors is the fact that the task’s internal error handling only reports the column lineage Id (ErrorColumn), not the column name of the column causing the error.

The main reason that the column Lineage Id can not be resolved is because it's not in the error pipeline. The error pipeline consists of the previous task's inputs plus two additional columns (ErrorCode and ErrorColumn). The column causing the error is probably created (or derived) in the previous task and then added to it's output collection.

In my search for a solution, I noticed that this was a common request among many ETL Developers. I ended up creating a custom task component to log data issues. The main concept is having only one custom component and connecting all tasks that have an error output path to the custom task component. When an error occurs, the component first looks in the input path of the error in hopes to also get the value causing the error (I have yet to have any data from this), then traverse all the other input paths to resolve the Column Name from the ErrorColumn. The name is resolved from a task that is down stream in the main pipeline. Another advantage to this approach is that I only have one task to configure per Data Flow instead of 3 per main task. This decreased our ETL development time by 80%.

I hope this explains why it’s hard (or impossible) to get the Column Name with a script task and possibly a direction to look to get it.

I referenced Chapter 14 of Professional SQL Server 2005 Integration Services to build the custom component.
Post #458696
Posted Wednesday, February 11, 2009 3:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 12:18 PM
Points: 5, Visits: 156
Jay (2/19/2008)
your code captures - error description from error code. Is there any way we can capture - actual error causing column name from error column ID?.


OK, so it's been a year.... any easier answers to this yet? :)
Post #655287
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse