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 «««7891011»»

Error Handling in SSIS Expand / Collapse
Author
Message
Posted Thursday, April 9, 2009 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #693987
Posted Tuesday, May 26, 2009 7:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:13 AM
Points: 853, Visits: 271
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
Post #723228
Posted Friday, July 3, 2009 12:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:13 AM
Points: 853, Visits: 271
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.







Post #746811
Posted Friday, July 3, 2009 12:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:13 AM
Points: 853, Visits: 271
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.







Post #746813
Posted Tuesday, July 7, 2009 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #748694
Posted Wednesday, July 15, 2009 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 2:44 PM
Points: 1, Visits: 25
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?
Post #753385
Posted Wednesday, July 29, 2009 5:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:04 AM
Points: 14, Visits: 264
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.
Post #762036
Posted Wednesday, July 29, 2009 7:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #762068
Posted Saturday, May 1, 2010 1:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:46 PM
Points: 54, Visits: 239
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>
Post #914210
Posted Saturday, May 1, 2010 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,235, Visits: 12,988
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #914264
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse