SSIS Data conversion Transformation editor - Null

Karen Schaefer 21 Reputation points

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,448 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 32,406 Reputation points

    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)  



    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,836 Reputation points

    Hi @Karen Schaefer ,

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

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