SSIS Data conversion Transformation editor - Null

Question

I am trying to use the Data Conversion Transformation(DCT) Editor (Visual Studio 2017) to handle the conversion of Varchar to integer and handle null values. I am getting the following errors: The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.", My research says that this is usually due to value = Null.

How do I use the DCT tool to handle the null values? Note: I need to convert the varchar to integer due to the destination table needs ID numbers to be used as FK going to connect the the FACT tables.

I am relativity new to SSIS and this tool.

Answer 1

Hi @Karen Schaefer ,

You may also try with below expression in Derived Column Transformation.

ISNULL(home_location_id) ? NULL(DT_I4) : (home_location_id == "" ? NULL(DT_I4) : (DT_I4)home_location_id)  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Karen Schaefer ,

You can try the following expression in the Derived Column Transformation:

ISNULL([col01]) || TRIM([col01]) == "" ? (DT_I4)0 : (DT_I4)[col01]