September 24, 2012 at 11:21 pm
Hi all,
i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.
but i want to move that record into error log file ....
thanks and regards
sathiyan.R
September 25, 2012 at 12:26 pm
sathiyan00 (9/24/2012)
Hi all,i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.
but i want to move that record into error log file ....
thanks and regards
sathiyan.R
Can you try to explain more clearly what you are trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2012 at 1:30 pm
sathiyan00 (9/24/2012)
Hi all,i have to move invalid records(data type mismatch) from excel into error log and have to process only valid records.
But the problem is when there is mismatch data type like :
in DT_WSTR there is value 1234
in DT_R8 there is value xxx while reading only the excel source replace it with null.
but i want to move that record into error log file ....
thanks and regards
sathiyan.R
There is a way to send errored rows to another drop location, which can then be manipulated. Go to your OLEDB delivery and click on error configuration, then choose redirect row for all options.
You'll then have to take the error rows and deliver them to a location that'll handle pretty much anything that might have ended up in the column.
Now, a few questions, because what you've listed above doesn't directly translate to that.
DT_WSTR (Unicode VARCHAR()) doesn't have a problem with 1234. What's the concern there?
DT_R8 (BIGINT if memory serves, but it's numeric) will properly error on xxx, but it won't translate to NULL for you. For something like that, you'll want to include a derived column in the datastream, replace the column, and have it detect for ISNUMERIC() ? OrigColumn : DT_R8NULL (it's in the syntax above in datatypes, I forget it offhand).
This isn't simply an 'if error transfer to drop file', you are looking to perform in stream transformations and detections if I understand this right, so it's not as simple. You have to build each direct check that isn't just a 'on bad data drop over here'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 25, 2012 at 11:57 pm
thanks all...
my problem is excel sheet is not reading the data of mismatch data type...
my excel data is
name phone
sathiyan 1234
rahul 12345
ssss 1344
yyyy xxx
when it reads fourth row it replace 'xxx' with null. but i want to move this record to error log using link comes from excel source...
September 26, 2012 at 12:10 am
[font="Times New Roman"]Hi Sathyan,
If i understand correctly the value 'XXX' is getting replaced by NULL in the excel sheet.
If thats the case can you give us some example for the values?
Thanks,
Karthick Mageswaran.[/font]
September 26, 2012 at 1:05 am
in the last post itself i given... that values only i have...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply