How to achieve TRY_CONVERT functionality in SSIS?

Question

In SSIS package I have derived column in which I want to format phone like below:

CASE
    WHEN TRY_CONVERT(BIGINT, phone) IS NULL THEN
        NULL
    ELSE
        phone
END

How can I use the SSIS expression to achieve same result as above?

Answer 1

Derived Column

You have to use the following expression:

(DT_I8)[Phone] == (DT_I8)[Phone] ? [Phone] : NULL(DT_WSTR,50)

Note that you have to replace (DT_WSTR,50) with the data type of column [Phone]. Click here for more information

And in the derived column error output change the on error option to Ignore Failure

Script Component

You can also achieve this using a script component:

1. Add a script component to the data flow task
2. Select [Phone] as Input Column
3. Create a new Output Column of the same type of [Phone] example [outPhone]

4. Use a similar code

   if(!Row.Phone_IsNull && !String.IsNullOrEmpty(Row.Phone) && Int64.TryParse(Row.Phone, out long number)){
   
       Row.OutPhone = Row.Phone;
   
   }else{
   
       Row.OutPhone_IsNull = true;
   
   }