如何检查ssis中条件拆分组件中的列是否为整数? [英] How to check if column is integer in conditional split component in ssis?

问题描述

我有以下 SSIS 包:

I have the following SSIS package:

在我的包中，平面文件源数据输入到条件拆分组件中.其中一列是 AccountNo.平面文件源输出数据类型中的该列是:string [DT_STR].

In my package, flat file source data feeds into a conditional split component. One of the columns is AccountNo. That column in the flat file source output datatype is: string [DT_STR].

如果 AccountNo 是一个整数，我想检查约束检查组件.例如，平面文件源中的那一列可能是这样的:

I want to check in the Constraint Check component if AccountNo is an integer. For example, maybe that column in the flat file source is this:

12355ss2rt3

并且该输入不是整数.我想将一个有效的整数传递给约束检查输出.在约束检查组件中，我有这个:

and that input is not an integer. I want to pass a valid integer number to the constraint check output. In the constraint check component, I have this:

此表达式使用类型转换组和 (DT_I8) 条件，但我收到此错误:

this expression uses Type Cast Group and (DT_I8) condition, but I get this error:

我该如何解决?

推荐答案

您可以使用派生列来做到这一点.

You can use derived column to do that.

在我的例子中，输入是:

In my example, the input is:

AccountNo
123
1234
123f
1e23

第 1 步:尝试将字符串转换为整数

只需使用派生列，尝试转换为 I8

Just use derived column, try to cast to I8

(DT_I8)AccountNo

第 2 步:配置错误输出，捕获错误行<​​/strong>

Step 2: Config error out, to catch error row

转换成功的行会进入 nomarl 流程，不能转换为 I8 的值会重定向到错误流程.一般的数据流是这样的(查看截图):数据流

The success casting rows will go to the nomarl flow, the value that can not cast to I8 will be redirect to the error flow. The general data flow is like this (view screenshot): Data flow

要获取错误值，您只需从驱动程序栏中拖放红色箭头即可.

To get the error value, you just drap and drop the red arrow from the driver column.

现在红色流包含不能转换为整数的值蓝色流是价值成功铸造的流.

Now the red flow is contain the value that can not cast to integer the blue flow is flow with value successful casting.

查看以下屏幕截图了解详情:

view following screenshot for detail:

截图 - 第 2 步配置错误输出

第 3 步:以您想要的方式处理未分配的价值

请注意，错误流的输出将为您提供 2 个有价值的列:

note that the output of error flow will give you 2 valuable columns are:

• [错误代码]
• [错误列]

让我们使用这些列来确保强制转换为整数失败"的错误.

Let use these column to make sure the error from "cast to integer fail".

您可以参考msdn文档查找ErrorCode.

You can refer to msdn document to lookup for ErrorCode.

基于ErrorColumn，您可以确定错误是否来自AccountNo列.

Base on ErrorColumn, you can make sure the error come from column AccountNo or not.

这篇关于如何检查ssis中条件拆分组件中的列是否为整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！