February 14, 2008 at 8:03 pm
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?.
February 14, 2008 at 10:11 pm
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
February 19, 2008 at 2:42 pm
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!!!!!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 5:25 pm
your code captures - error description from error code. Is there any way we can capture - actual error causing column name from error column ID?.
February 19, 2008 at 7:09 pm
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.........
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 8:23 pm
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
February 20, 2008 at 2:28 pm
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
February 20, 2008 at 3:17 pm
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
February 21, 2008 at 10:49 am
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.
February 11, 2009 at 3:19 pm
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? 🙂
February 4, 2010 at 5:29 pm
ok - so its been 3 years - any simpler answers to this yet?
March 19, 2010 at 1:58 pm
try this, hope it helps u..... : http://eod.codeplex.com/
March 19, 2010 at 2:00 pm
Fantastic, thank you!
March 1, 2011 at 5:27 pm
This is perhaps a stupid question, but when you add a custom component, do you just need to add it on your computer or do you need to add it to the production server as well?
March 2, 2011 at 5:35 am
The custom component needs to be installed on the development PC, which I’m assuming is your machine, and the production machine that SSIS is running on.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply