ssis筛选出以字母开头的值的行 [英] ssis filter out rows with values starting in a letter

问题描述

在SSIS项目中，我试图从Excel文件源中过滤出行，其中一列的值以字母开头，后跟数字.一些单元格包含多个值，并且并非所有单元格都遵循数据类型格式.当前数据流如下:
Excel Source > Data Conversion > OLE DB Destination
我在Excel Source之后添加了条件拆分，但是我对如何过滤掉不需要的记录感到困扰.以下是在数据流结束之前不应该包含的值的示例:

In an SSIS project I am trying to filter out rows from an Excel file source where a column has values that start in a letter and followed by numbers. Some cells contain more that one value and not all cells follow a data type format. The data flow is currently as follows:
Excel Source > Data Conversion > OLE DB Destination
I am adding a Conditional Split after the Excel Source, but I am troubled with how to filter out unneeded records. Below are examples of values that should not be included before the end of the data flow:

Row      Value
1        1234
2        P123
3        P1234, P456
4        rec P678

第1行应该是唯一应流入目的地的行.有没有一种方法可以过滤掉以'P'开头并以数字开头的记录，而不管每个单元格中有多少个值?

更新:我目前正在解决FINDSTRING(Value，"P"，1)> 0 || FINDSTRING(Value，"p"，1)>0.输出阻止第2-4行，但第1行的Value更改为0.有人知道为什么会这样吗?

Row 1 should be the only one that should flow to the destination. Is there a way to filter out records that start with 'P' and followed by numbers, regardless of how many values in each cell?

Update: I'm currently working around FINDSTRING(Value,"P",1) > 0 || FINDSTRING(Value,"p",1) > 0. The output blocks rows 2-4 but the Value for row 1 was changed to 0. Does anybody know why this happens?

推荐答案

第一个解决方案

具有.Net方法的脚本组件，用于检查值是否为数字.

1)脚本组件 在需要进行检查的地方添加一个脚本组件(类型:转换).

1) Script Component Add a Script Component (type: transformation) where you need to do the check.

2)选择输入列 将需要检查的列添加为输入列.

2) Select Input Columns Add the column that needs to be checked as input column.

3)添加输出列 在输入和输出"选项卡上的输出"列中添加一个新列.拍摄类型为布尔型，并为其指定合适的名称.

3) Add Output Column Add a new column to the Output columns on the tab Inputs and Outputs. The type shoot be Boolean and give it a suitable name.

4)脚本

'VB.Net代码 '检查字符串值是否包含数字 进口制度 导入System.Data 导入System.Math 导入Microsoft.SqlServer.Dts.Pipeline.Wrapper 导入Microsoft.SqlServer.Dts.Runtime.Wrapper

' VB.Net code ' Check whether the string value contains a number Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Standard VB.net method, indicating whether
    ' an expression can be evaluated as a number
    If (IsNumeric(Row.InputCol)) Then
        Row.IsNumeric = True
    Else
        Row.IsNumeric = False
    End If
End Sub

End Class

5)创建条件拆分以过滤行

5) Create a conditional split to filter Rows

派生列

1)添加派生列 在需要进行检查的地方添加派生列.

1) Add Derived Column Add a Derived Column where you need to do the check.

2)添加表达式 添加具有以下表达式的新列，并为其指定适当的名称:!ISNULL((DT_I8)TextNumbers).所有数字都将为True，所有非数字都将引发错误.

2) Add Expression Add a new column with the following expression and give it a suitable name: !ISNULL((DT_I8)TextNumbers). All numbers will result in True and all non-numbers will raise an error.

3)忽略错误 转到派生"列中的配置错误输出"窗口，并忽略新字段的错误.

3) Ignore error Go to the Configure Error Output window in the Derived column and ignore errors for the new field.

4)结果 添加条件拆分以使用ISNULL表达式

4) The Result Add a conditional split to filter rows using ISNULL expression

数据转换

1)具有数据转换 第二种解决方案的替代方法是尝试通过数据转换转换将值转换为int并忽略任何错误.比起添加条件拆分来使用ISNULL表达式

1) with Data Conversion An alternative to the second solution could be to try convert the value to an int via a Data Conversion Transformation and also ignore any errors. Than add a conditional split to filter rows using ISNULL expression

这篇关于ssis筛选出以字母开头的值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！