Skip columns in BIML SSIS Script

Question

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>

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>

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

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

although not elegant, i found a solution.<DirectInput>SELECT <#=table.GetColumnList(c => c.Name != "dwh_timestamp")#> , GETDATE() as dwh_timestamp FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput> — phicon

Cathrine Wilhelmsen Cathrine Wilhelmsen · Accepted Answer · 2016-02-22T09:02:04

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")#>

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.

Skip columns in BIML SSIS Script

1 Answers