如何在SSIS脚本组件中按名称循环遍历列？ [英] How can I loop though columns by name in an SSIS Script component?

问题描述

我正在将管道分隔的平面文件加载到临时表中。在加载过程中，SSIS脚本组件在一行上执行一些操作。它可以根据另一个字段中的值在一个字段中设置标志，为某些列添加前缀，或应用格式。例如，如果缺少日期，则将该字段分配给默认日期。 （如果Row.EndDate_isNull，则Row.EndDate = defaultDate）

I'm loading a pipe delimited flat file into a staging table. During the load process an SSIS script component performs some operations on a row. It may set a flag in one field based on values in another field, add a prefix to certain columns, or apply formatting. For example, if a date is missing, the field is assigned to a default date. (if Row.EndDate_isNull then Row.EndDate = defaultDate)

当需要将相同的转换应用于一系列行时，这些脚本变得很麻烦。例如，病历文件可以用三个字段描述9个诊断中的每一个：Diagnosis01_Date，Diagnosis01_Code，Diagnosis01_System .... Diagnosis09_Date，Diagnosis09_Code，Diagnosis09_System。

These scripts become cumbersome when the same transformation needs to be applied to a series of rows. For example, a medical record file can describe each of 9 diagnoses with three fields: Diagnosis01_Date, Diagnosis01_Code, Diagnosis01_System....Diagnosis09_Date, Diagnosis09_Code, Diagnosis09_System.

我想使用循环对3个字段的9组中的每组执行操作，而不是将相同的操作编写9次。

I want to use a loop to perform the operations over each of the 9 groups of 3 fields, instead of writing the same operation 9 times.

例如，如果我在VB中处理集合，我会在Input0_ProcessInputRow子目录中这样写：

If I were dealing with a collection in VB, for example, I'd write something like this in in the Input0_ProcessInputRow sub:

For i = 1 to 9
   row.("Diagnosis0"+ i + "_Date").Value = diagnosisDate
   row.("Diagnosis0"+ i + "_System").value = "ICD10"
next i 

但是，在SSIS对象模型中，这些列作为Input0Buffer的属性公开，我找不到使用变量引用它们的方法。因此，如何 如何创建一个循环，该循环按名称在SSIS脚本组件中对列进行操作？

In the SSIS object model, however, the columns are exposed as properties of Input0Buffer and I can't find a way to use a variable to refer to them. So, how can I create a loop that operates on columns by name in an SSIS script component?

编辑：在研究此问题时，我发现以下来源，尤其是前两个来源会有所帮助。似乎应该有一个使用system.reflection的解决方案，但我只是不太了解.NET，因此无法解决它。

I found the following sources, especially the first two, to be helpful while I was doing my research on this problem. It seems like there should be a solution using system.reflection, but I just don't know .NET well enough to figure it out.

> http://agilebi.com/jwelch/2007/10 / 21 / address-columns-generically-in-a-script-task /

> http://agilebi.com/jwelch/2007/06/02/xml-destination-script-component/

http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html

http://toddmcdermid.blogspot.com/2011/05/iterating-over-columns-in-ssis-script.html

http://bidn.com/blogs/MikeDavis/ssis/1800/ssis-for-each-column-in-a-data-flow

https://social.msdn.microsoft.com/Forums/zh-CN/edbac1df -f05f-40db-820a-e009fae201a4 /使用脚本目标对象创建并写入新文本文件？forum = sqlintegrationservices& forum = sqlintegrationservices

https://social.msdn.microsoft.com/Forum s / zh-CN / 757d11c8-8ad4-4021-a959-1d13c8dfdaa7 /如何为每个列的脚本组件输入列集合中的所有列运行循环？ forum = sqlintegrationservices

如何在SSIS中的脚本组件中获取值列？

推荐答案

简单的解决方法

您可以使用循环将列名称存储在列表（字符串）中，并使用 Row.GetType（）。GetProperties（）动态地操作列。

Simple Workaround

You can store columns name in a List(of string) using loops , and use Row.GetType().GetProperties() to manipulate columns dynamically.

示例：

注意：您必须导入 System.Reflection ， System.Linq 和 System.Collections.Generic 库

Dim lstDateColumns as new List(of string)
Dim lstSystemColumns as new List(of string)

For i = 1 to 9
    lstDateColumns.Add("Diagnosis0" & i.toString() & "_Date")
    lstSystemColumns.Add("Diagnosis0" & i.toString() & "_System")
Next


For each  dataColumn as PropertyInfo in Row.GetType().GetProperties()


    If lstDateColumns.Contains(dataColumn.Name) Then

                 dataColumn.SetValue(Row, diagnosisDate, Nothing)

     ElseIf lstSystemColumns.Contains(dataColumn.Name) Then

                dataColumn.SetValue(Row, "ICD10", Nothing)

     End IF
Next

您可以过滤列表中的列名

And you can filter over columns names from the lists

    Dim lstDateColumns As New List(Of String)
    Dim lstSystemColumns As New List(Of String)

    For i As Integer = 1 To 9
        lstDateColumns.Add("Diagnosis0" & i.ToString() & "_Date")
        lstSystemColumns.Add("Diagnosis0" & i.ToString() & "_System")
    Next

    For Each dataColumn As PropertyInfo In Row.GetType().GetProperties().Where(Function(x) lstDateColumns.Contains(x.Name))

        dataColumn.SetValue(Row, diagnosisDate, Nothing)

    Next


    For Each dataColumn As PropertyInfo In Row.GetType().GetProperties().Where(Function(x) lstSystemColumns.Contains(x.Name))

        dataColumn.SetValue(Row, "ICD10", Nothing)

    Next

参考文献

• https://waheedrous.wordpress.com/2014/02/24/ssis-global-使用脚本组件替换所有列/

• https://waheedrous.wordpress.com/2014/02/24/ssis-global-replace-for-all-columns-using-a-script-component/

这篇关于如何在SSIS脚本组件中按名称循环遍历列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！