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?

Recalcitrate

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;
   
   }
   

Meaghanmeagher

It's making NULL for all the values of Phone. – Recalcitrate

What is the type of the source column? – Meaghanmeagher

Check that [Phone] doesn't contains unwanted characters or additional spaces – Meaghanmeagher

@JayDesai the try convert logic is 100% correct you must check the data – Meaghanmeagher

Phone column does contain some values with unwanted characters or additional spaces. And that's the reason I want to make it NULL. If it is convertible to BIGINT it should pass it as it is. Source column Phone is coming from csv file and Destination column has BIGINT data type. – Recalcitrate

TRY_CONVERT function is working correctly I need the same functionality in SSIS. – Recalcitrate

@JayDesai what is the column data type in SSIS? Check it from the flat file connection manager – Meaghanmeagher

@JayDesai does numbers contains a seperator in csv? – Meaghanmeagher

@JayDesai i updated my answer and provided a new method check it out – Meaghanmeagher

@JayDesai also it is better to provide sample data and to add some screenshot of the flat file connection manager – Meaghanmeagher