如何在SSIS中实现TRY_CONVERT功能? [英] How to achieve TRY_CONVERT functionality in SSIS?

问题描述

在SSIS程序包中，我派生了一个专栏，希望在其中格式化手机，如下所示:

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

如何使用SSIS表达式获得与上述相同的结果?

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

推荐答案

派生列

您必须使用以下表达式:

Derived Column

You have to use the following expression:

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

请注意，您必须将(DT_WSTR,50)替换为列[Phone]的数据类型.

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

然后在派生列错误输出中，将on error选项更改为Ignore Failure

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

您也可以使用脚本组件来实现此目的:

You can also achieve this using a script component:

1. 向数据流任务添加脚本组件
2. 选择[电话]作为输入列
3. 创建与[Phone]示例[outPhone]相同类型的新输出"列

4. 使用类似的代码

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;

}

这篇关于如何在SSIS中实现TRY_CONVERT功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！