Search code examples
sql-serverssisbiml

Nested queries in BIML

I'm trying my hand at BIML and the path is full of trial and error.

I have to transfer data from fixed length flats files and I stored the metadata for my files and fields in two SQL Server tables: SourceTable and SourceColumn. So I have tried the following to generate the metadata for my fields:

<Tables>
<# Dim dtTables as DataTable = ExternalDataAccess.GetDataTable(staging.ConnectionString, "SELECT TableName FROM SourceTable")
for each dr as datarow In dtTables.Rows#>
<Table Name="<#= dr.item(0)#>" SchemaName="myDatabase.mySchema">
<Columns>
<# Dim dtColumns as DataTable = ExternalDataAccess.GetDataTable(staging.ConnectionString,"SELECT ColumnName FROM SourceColumn WHERE TableName = '+<#=dr.item(0)#>+'")
for each drColumn as datarow in dtColumns.Rows#>
<Column Name="<#= drColumn.Item(0) #>"></Column>
<#next#>
</Columns>
</Table>
<#next#>
</Tables>

Of course, BIML doesn't want me to concatenate <#=dr.item(0)#> (the table name) in the middle of the second query.

Is there a way to do that?

Thank you!

Simon.

Solution

  • When you're inside the <# you are in "normal" coding space so you would have access to whatever variables are available

    Change

    Dim dtColumns as DataTable = ExternalDataAccess.GetDataTable(staging.ConnectionString,"SELECT ColumnName FROM SourceColumn WHERE TableName = '+<#=dr.item(0)#>+'")

    to

    Dim dtColumns as DataTable = ExternalDataAccess.GetDataTable(staging.ConnectionString,"SELECT ColumnName FROM SourceColumn WHERE TableName = '" & dr.item(0) & "'")

    And yes to the future commenters, this could be risky if someone added sql injection into your table but if that's happened, you're already owned.