SSIS - Data Conversion Transformation/Derived column - Varchar to Integer, Null
-
I am relatively new to SSIS and I need to convert a varchar to and integer within SSIS that will handle Null values.
I have research numerous websites with out success.
How do I convert a varchar to integer and still handle the if there is a null value? I have tried both Derived Column Transformation Editor & the Data Conversion Transformation editor, however I do no see where to indicate how to handle null values.
Source:
Destination:
As you can see from the Source file and Destination file the difference in field types.
Error msg which points me to possible Null value issue:
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Hoping you can point me in the right direction, any assistance offered is greatly appreciated.
Thanks,
Karen
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I'm bumping this for you, Karen. Seems like it should be simple to do in SSIS but I don't use SSIS and can't help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Are you sure it's blowing up because it's null? Might want to try a simple select * from stg.users where try_convert(int,home_location_id) is null.
I'm sure the syntax is wrong there.
I've run into issues like this before where it was actually a weird value in the column. tab's etc.
And side note, I've used SSIS for about 10 years and I despise transformations. I do almost anything that requires conversion in T-SQL.
-
Thanks for the input, found solution on another forum.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply