February 11, 2008 at 8:18 am
Hi Guys,
1) I have data coming in from flat file everyday.On failure I would require Error Table to be updated with problematic rows.Otherwise rows would be updated to a Staging Table.Problem is I have been asked to also return the row number for the problematic rows, so that it's easier for the Client to resend failed rows.How do I do this ?
2) I have realize that even if row coming in has some invalid data type in more than 1 column.ErrorColumn, in Error table just return 1 column value.Is this how it works? OR is there a way for it to return the Error_Column for the other Column's as well.
I have been struggling with this since last week.Do help me out.:sick:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 13, 2008 at 2:15 am
Common problem, unfortunately this is the way it's meant to work (ie. you can only identify one problem column at a time.
One method that might be worth considering depending on your data would be do some cleansing from the start. You need to do a decent amount of sampling through your data to find the different ways it can get hung up (ie. nulls, invalid dates, truncated strings, etc). Based on the trends you can do conditional splits or derived columns when those circumstances occur and modify the data to bring it into compliance. For example, if you have nulls and your destination tables doesn't allow them say on a numerical field you can change the null to 0, of if the string length is too long you could do a substring of the inbound column to only grab character 1 thru the max length of your destination field.
Otherwise, your stuck correctly one problem at a time and seeing if it fails on anything else in the same row.
February 13, 2008 at 1:15 pm
Hmmm..pretty tedious process Mate...
I came across this today
http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx
Planning to give it a try and see how it works out for me.:ermm:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 14, 2008 at 9:40 am
Adding to the ideas of WillyWonka. Try this site
http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20050512SQLServerDF/manifest.xml
Extract from the site page:
"Data quality is a critical issue in data integration. Donald Farmer introduces the data quality features of SQL Server Integration Services with demos that show how to use the Fuzzy Lookup component for inexact matching"
The presentation is a video with an integrated transcript.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply