convert string allow null col to int?

Question

I want to convert string to int my string type [DT_WSTR] and my expression

ISNULL(col) || TRIM(col) == "" ? (DT_I4)0 : (DT_I4)col

I get this error [Derived Column [518]] Error: The conditional operation failed.

Answer 1

You've got something that doesn't convert to an integer value in your source data.

Since the expression language doesn't have a tryparse method, the cleanest mechanism is to use a script component as a Transformation and leverage the .NET methods.

1. Connect a Script Component to your source.
2. On the Input Columns tab, check col as ReadOnly.
3. On the Inputs and Outputs tab, expand Output 0, click Output Columns and select Add Column
4. Rename this column to CleanCol and leave with the default data type of DT_I4
5. Click back to the Script tab and click Edit Script...
6. Use the following method in your code

Line here so formatting picks up

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int colOut = 0;
    if (!Int32.TryParse(Row.col, out colOut))
    {
        Row.CleanCol_IsNull = true;
    }
    else
    {
        Row.CleanCol = colOut;
    }
}

Here's my source query

SELECT
    D.*
FROM
(
VALUES
    (N'1')
,   ('')
,   ('0')
,   ('3')
,   ('X') -- Uncomment this one to generate error
,   (NULL)
) D(col);

Results

As I think about it a bit longer, in the above script, if you wanted to have zero outputted for "bad" values, replace the first action in the if statement with Row.CleanCol = 0; Otherwise, this will make numbers of numbers and NULL of non-numbers.