April 9, 2009 at 7:42 am
Laura Lin (4/7/2009)
Would you give out step by step instruction on how to install and set up your error handling component? Thanks
Laura,
Sorry it took so long to get to this. This is partially because I didn't remember all the exact steps and had to look it up. Then since I knew I'd forget again since I don't do it often I figured I'd put it on my blog so I would have a place to go to look it up quickly. So here's the blog post with the steps (I assume you already downloaded and unzipped it).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 26, 2009 at 7:03 am
hi,
how to handle the special character in the xml file. i couldnt able to capture if the data comes as special character. is there any workaround for this. can you please let me know how to handle this?
Thanks
Balaji L
July 3, 2009 at 12:25 am
Hi,
Can anyone please post the script component code. I couldnt able to open the solution file.
I am using sql server 2005 and VS 2005
I took the code from the article (initial post), but the "name" field is not appeared.
July 3, 2009 at 12:26 am
Balaji (7/3/2009)
Hi,Can anyone please post the script component code. I couldnt able to open the solution file.
I am using sql server 2005 and VS 2005
I took the code from the article (initial post), but the "name" field is not appeared.
July 7, 2009 at 10:05 am
The custom component was done in VS2008 so it won't open in VS2005. You could open it in a text editor to see the code.
I'm not sure what you mean here:
I took the code from the article (initial post), but the "name" field is not appeared.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2009 at 6:45 am
Great component! Just what I have been looking for!
The error step field does not seem to get populated. I don't see anything in the code to fill it.
Is there a version of the script that has been updated since the article went on line?
July 29, 2009 at 5:15 pm
Thank you for the article.
I implemented it with idea that I would include the source row's key field in the xml column - in this way one could look up the row (using the key) in the source to check the data. I did my simple test by defining one of the destination fields non null and then re-directed rows where the source value for this field was null. It worked as planned, except I am disappointed in the lack of detail provided by - ComponentMetaData.GetErrorDescription(Row.ErrorCode). When you let the data flow error without re-direction code in place it can tell you the field that violates the contraint but using the above method all that is returned is "The data value violates integrity constraints." (I was hoping for details).
I definitely plan to use this technique and much appreciate - besides handling nulls and constraints would be something planned for anyway.
Sartre- we are always "more" than our situation and that this is the ontological foundation of our freedom.
July 29, 2009 at 7:10 pm
Brad,
I was also disappointed with the lack of details, which is why I output the entire row to the XML column. That way I can see the data and usually quickly pinpoint the offending column(s).
There may be even more information available, I have not worked with SSIS much lately, so I have not investigated further.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 1, 2010 at 1:05 am
Great article! I have a question, is there a way to query the xml column and have the field names and values be cross-tabbed?
So here is my error detail below for a single row, I would to have a query dynamically crosstab the fieldnames and values, is this possible? So my query would have the error table columns plus these extra columns of data. This would make it easier to look at the data and troubleshoot it.
I noticed you posted a Report, maybe this report does what I am asking, but I don't know much about Reporting services, or SSIS for that matter.
example
<fields>
<field name="CREDIT_LIMIT" value=" 10001.76" />
<field name="SALES_ID" value="554147" />
<field name="DATE_BOARDED" value="A0100201" />
<field name="PROVIDER_ID" value=" 4" />
<field name="BUSINESS_NAME" value="BOPE LLC " />
<field name="CUSTOMER_LAST_NAME" value="BOPE " />
<field name="CUSTOMER_FIRST_NAME" value="SCOTT A " />
<field name="BRANCH_NUMBER" value="21293 " />
</fields>
May 1, 2010 at 1:44 pm
Stringzz (5/1/2010)
Great article! I have a question, is there a way to query the xml column and have the field names and values be cross-tabbed?So here is my error detail below for a single row, I would to have a query dynamically crosstab the fieldnames and values, is this possible? So my query would have the error table columns plus these extra columns of data. This would make it easier to look at the data and troubleshoot it.
I noticed you posted a Report, maybe this report does what I am asking, but I don't know much about Reporting services, or SSIS for that matter.
example
<fields>
<field name="CREDIT_LIMIT" value=" 10001.76" />
<field name="SALES_ID" value="554147" />
<field name="DATE_BOARDED" value="A0100201" />
<field name="PROVIDER_ID" value=" 4" />
<field name="BUSINESS_NAME" value="BOPE LLC " />
<field name="CUSTOMER_LAST_NAME" value="BOPE " />
<field name="CUSTOMER_FIRST_NAME" value="SCOTT A " />
<field name="BRANCH_NUMBER" value="21293 " />
</fields>
Thanks for the compliment. The Reporting Service report does not cross tab the xml, although you could do it. I think if you read the 2 cross-tab articles linked in my signature you should be able to figure out a way to do it. I don't know enough xml to know if you could do it using xml.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 4, 2010 at 11:31 am
Jack, I was able to come up with a way to crosstab the xml values. You could also use this to run a report I guess.
Make sure to change the ErrorTask and RunDate variables to values in your own load_errors table.
You might want to add this to your original post.
Thanks again
BEGIN
-- Define Variables
DECLARE @ListCol VARCHAR (1000)
, @Query VARCHAR (2000)
, @ErrorTask VARCHAR (100)
, @RunDate VARCHAR (100)
SET @ListCol = ''
SET @Query = ''
SET @ErrorTask = 'DFT Load Credit Card'
SET @RunDate = '2010-04-29 14:08:00'
-- Create dynamic column list given Error Task name
SELECT @ListCol = (SELECT DISTINCT a.name + ','
FROM load_errors b
cross apply error_details.nodes('//field') AS xmlrows(data)
cross apply (SELECT xmlrows.data.value('./@name[1]','varchar(120)') AS field_name
) AS a(name)
WHERE b.error_task = @ErrorTask
FOR XML PATH(''))
SET @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)
-- Create dynamic query
SET @Query = '
SELECT *
FROM (SELECT t.load_error_id
, t.package_run_date
, t.error_task
, t.error_step
, t.error_code
, t.error_desc
, f.name
, f.value
FROM load_errors t
--= blow out the rows from the xml column
cross apply error_details.nodes(''//field'') as xmlrows(data)
--= convert the nodes to two columns, field: name and value
cross apply (select xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name
, xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value ) as f(name,value)
WHERE t.error_task = ''' + @ErrorTask + '''
AND t.package_run_date = ''' + @RunDate + '''
) inner_table
pivot (max(inner_table.value)
for inner_table.name in (' + @ListCol + ')
) pvtfname
'
-- Execute dynamic query
EXEC(@QUERY)
END
December 16, 2010 at 4:26 pm
Its a wonderful article.
I learned a lot.
Tinku
September 28, 2012 at 12:50 pm
This article fails to mention this technique will only log error messages generated within SSIS. Errors reported by the database engine (including SQL Server) will receive the generic "no status is available" error. In those cases, you cannot get the detailed error in the redirect that you would normally see if a package was set to fail on error. This is just another example of functionality included in most ETL tools, but lacking in SSIS.
October 3, 2012 at 7:55 am
Shon,
In my experience, I have gotten the error message generated by SQL Server back when using this. It's why I wrote the code. The errors I was experiencing were from SQL Server, like Data Type overflow errors, which are coming from SQL Server.
What types of errors are you seeing that aren't getting descriptions returned?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2012 at 1:40 pm
It seems like the issue is column level vs row level errors. The method call is showing predefined errors, but does not return the same message you see from the db engine. Here are two examples.
1. I have a unique index setup on a table. When we try to insert a dup, the error raised by the db engine is "Cannot insert duplicate key row in object 'TableName' with unique index 'Index_Name'. The duplicate key value is ...." The error we get in SSIS is "The data value violates integrity constraints. "
2. I have a trigger on a table that checks for overlapping dates, and raises a 16 level error with rollback. Instead of getting the error message generated in the trigger, we get "No status is available".
In searching for a method to return the error generated by the db, I find that it is not possible in SSIS. What makes it even more disappointing is that we know SSIS does have these errors at the package level because we get them in a package level OnError event handler if the package is setup to fail on error, but there is apparently no way to have this information at the row level during a redirect. I hope I am wrong about this, but at this point I have not found a way to do it in SSIS even though it would be a simple tsql script to accomplish the same thing.
Viewing 15 posts - 91 through 105 (of 107 total)
You must be logged in to reply to this topic. Login to reply