URGENT!! error handling for data conversion errors at the source

Question

URGENT!! error handling for data conversion errors at the source

s.kandepi3

Mr or Mrs. 500

Points: 581
More actions
August 14, 2015 at 11:27 pm

#319843

my package gave me this error.. the varchar value xyz cannot be converted to datatype int.. how to do error handling for such conversion errors? I tried the following ways.. but none of them worked
1. Redirect the error rows at the OLE DB source to another table with derived column transform in between
2. Cast the column causing error to varchar in the OLEDB source; Using a derived column, converting it to int again and replacing it. From this derived column, i tried to redirect the error rows.
None of them worked. Any suggestions on how to do error handling for this case?
PS: The source contains a view and the primary source of this column is a table from the view. I can cast it in the view, but what if some other packages are using the same view? Is there a way I can do it without modifying the view?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

s.kandepi3 (8/14/2015)
my package gave me this error.. the varchar value xyz cannot be converted to datatype int.. how to do error handling for such conversion errors? I tried the following ways.. but none of them worked
1. Redirect the error rows at the OLE DB source to another table with derived column transform in between
2. Cast the column causing error to varchar in the OLEDB source; Using a derived column, converting it to int again and replacing it. From this derived column, i tried to redirect the error rows.
None of them worked. Any suggestions on how to do error handling for this case?
PS: The source contains a view and the primary source of this column is a table from the view. I can cast it in the view, but what if some other packages are using the same view? Is there a way I can do it without modifying the view?

Your source can be a query. So you can put whatever SQL you like in there to handle these problems up front.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

s.kandepi3 Mr or Mrs. 500 Points: 581 More actions · Answer 2

Thanks! I was trying to do that but I am not able to figure out one thing. I have a column xyz which is defined as char in all the tables it is coming from in the query(I have an ID which is coming from different tables.. and I am picking the ID from different tables based on a condition in case statement). But I still get this error at source cannot convert varchar to int. I am not able to figure where it is actually getting converted to int as it is varchar in all the tables .

s.kandepi3 Mr or Mrs. 500 Points: 581 More actions · Answer 3

found it.. it is getting converted to int in the view. so removed the cast in the view and let it flow as varchar.. and then in the derived column converted it to int and redirected the error rows . But wondering is there any way i do this without altering the view?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

s.kandepi3 (8/15/2015)
found it.. it is getting converted to int in the view. so removed the cast in the view and let it flow as varchar.. and then in the derived column converted it to int and redirected the error rows . But wondering is there any way i do this without altering the view?

Of course there is. Script the view definition, modify it to do what you need, and use that as your source query. This way, you are not relying on the view at all.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

s.kandepi3 Mr or Mrs. 500 Points: 581 More actions · Answer 5

I guess that would be possible if I am using just the view as my source .. by my source query is kinda complicated. the view gets joined with another and then there is the where exists condition with a subquery in it. I guess with this scenario, doing what you said is not possible, if i am not wrong. Let me know

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

s.kandepi3 (8/16/2015)
I guess that would be possible if I am using just the view as my source .. by my source query is kinda complicated. the view gets joined with another and then there is the where exists condition with a subquery in it. I guess with this scenario, doing what you said is not possible, if i am not wrong. Let me know

I would be surprised if it were not possible.

--Currently you have something like this

select .....

from t1

join v1 on t1.Id = v1.id

--Which can be converted to

select .....

from t1

join ([enter v1 view definition here]) v1 on t1.Id = v1.id

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin