跳过 BIML SSIS 脚本中的列 [英] Skip columns in BIML SSIS Script

问题描述

我使用以下 BIML 脚本根据源数据库为每个表创建一个列列表.我还添加了 dwh_timestamp 列.我使用此脚本创建目标表，添加元数据列.

I use the following BIML script to create a list of columns per table based on the source database. I also add the column dwh_timestamp. I use this script to create the target tables, adding a metadata column.

  <Columns>
    <# foreach (var column in table.Columns) { #>
        <#=column.GetBiml()#>
    <# } #>
    <Column Name="dwh_timestamp" DataType="DateTime" IsNullable="true"/>
  </Columns>

我使用以下代码创建将从源导入数据的 SSIS 包.

I use the following code to create the SSIS packages that will import data from the source.

<DirectInput>SELECT <#=table.GetColumnList()#> FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput>

但这会导致错误，因为源表不包含 dwh_timestamp 列.

This however results in an error as the source tables do not contain the dwh_timestamp column.

我将如何过滤 table.GetColumnList() 以跳过带有dwh_"前缀的列?

How would i filter the table.GetColumnList() so it skip the columns with the "dwh_" prefix?

推荐答案

在 Biml 中有几种方法可以过滤列列表.

There are several ways to filter a column list in Biml.

您可以过滤列名或部分列名:

You can filter on column names or parts of column names:

<#=table.GetColumnList(c => c.Name != "dwh_timestamp")#>
<#=table.GetColumnList(c => c.Name.StartsWith("dwh_"))#>

更可重用的解决方案是在列上创建注释并在注释上过滤:

A more reusable solution is to create Annotations on the columns and filter on the annotation:

<Columns>
  <# foreach (var column in table.Columns) { #>
    <#=column.GetBiml()#>
  <# } #>
  <Column Name="dwh_timestamp" DataType="DateTime" IsNullable="true">
    <Annotations>
      <Annotation AnnotationType="Tag" Tag="IsDWHColumn">Yes</Annotation>
    </Annotations>
  </Column>
</Columns>

<#=table.GetColumnList(c => c.GetTag("IsDWHColumn") != "Yes")#>

当然，您可以选择自己的注释策略.您可能希望使用true"和false"而不是Yes"和No"，或者反转注释逻辑以指定哪些列是源列而不是 DWH 列.

You choose your own annotation strategy, of course. You may want to use "true" and "false" instead of "Yes" and "No", or reverse the annotation logic to specify which columns are source columns instead of DWH columns.

这篇关于跳过 BIML SSIS 脚本中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！