Unable to create connection in Biml

I just started using BIML and I am not able to create a proper database connection. In the example below - when I call RootNode.Connections[] it throws : The supplied connection is null and the RootNode.Connections.Count() is 0.

I was able to work-around the issue by using SchemaManager.CreateConnectionNode (Scenario 2 in the code below) , but I wonder we the connection defined in the BIML does not work.

I am using VS2019(SQL Server Integration Services Projects 3.16), latest bimlexpress

The code below :

<Biml xmlns="http://schemas.varigence.com/biml.xsd">    
    <Connections>        
        <Connection Name="POC_BIML_1" ConnectionString="Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;"/>    
    </Connections>  
<!-- Scenario 1: RootNode.Connections -->    

<# var sourceConnection1 = RootNode.DbConnections["POC_BIML_1"];
   if (sourceConnection1 == null ) { #>
            <!--sourceConnection1 is null. RootNode.Connections.Count = <#=RootNode.Connections.Count() #> -->
    <#}
    else {        
        var sourceMetadata1 = sourceConnection1.GetDatabaseSchema();
        foreach (var table in sourceMetadata1.TableNodes) { #>
             <!--   <#=table.Name#>" -->!
           <# } 
    }#>    
<!-- Scenario 2: SchemaManager.CreateConnectionNode -->     
<# var sourceConnection2 = SchemaManager.CreateConnectionNode("POC_BIML_2", @"Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;");
  var sourceMetadata2 = sourceConnection2.GetDatabaseSchema();    
 foreach (var table in sourceMetadata2.TableNodes) { #>
        <!--<#=table.Name#>"-->!        
        <#} #>
</Biml>

returns :

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="POC_BIML_1" ConnectionString="Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;"/>
  </Connections>
  <!-- Scenario 1: RootNode.Connections -->
  <!--sourceConnection1 is null. RootNode.Connections.Count = 0 -->
  <!-- Scenario 2: SchemaManager.CreateConnectionNode -->
  <!--care_provider_import_histories"-->!        
                
  <!--care_provider_metrics"-->!        
                
  <!--care_providers"-->!        
                
  <!--care_services"-->!        
        


</Biml>

Solution

This is a tiering issue. To be able to reference something, you need to have done it in a previous step (or tier).

A static file is tier 0 so if you have

<Biml xmlns="http://schemas.varigence.com/biml.xsd">    
    <Connections>        
        <Connection Name="POC_BIML_1" ConnectionString="Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;"/>    
    </Connections> 
</Biml>

Sitting in 0_Connections.biml that's going to be Tier 0 always because there's no dynamic code in there (no Biml nuggets, expressions, etc)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">    
    <Connections>        
        <Connection Name="POC_BIML_<#=1#>" ConnectionString="Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;"/>    
    </Connections> 
</Biml>

Now that "same" file has a single expression building the Name attribute. That's now a dynamic script and tier 1, unless you tell it otherwise.

This is me being explicit with a tier in case you need to have additional layers. Note that I've stripped out the Connections declaration because that's in our other file.

<#@ template tier="10" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">    
<!-- Scenario 1: RootNode.Connections -->    

<# var sourceConnection1 = RootNode.DbConnections["POC_BIML_1"];
   if (sourceConnection1 == null ) { #>
            <!--sourceConnection1 is null. RootNode.Connections.Count = <#=RootNode.Connections.Count() #> -->
    <#}
    else {        
        var sourceMetadata1 = sourceConnection1.GetDatabaseSchema();
        foreach (var table in sourceMetadata1.TableNodes) { #>
             <!--   <#=table.Name#>" -->!
           <# } 
    }#>    
<!-- Scenario 2: SchemaManager.CreateConnectionNode -->     
<# var sourceConnection2 = SchemaManager.CreateConnectionNode("POC_BIML_2", @"Data Source=.;Initial Catalog=POC_BIML;Provider=SQLNCLI11;Integrated Security=SSPI;");
  var sourceMetadata2 = sourceConnection2.GetDatabaseSchema();    
 foreach (var table in sourceMetadata2.TableNodes) { #>
        <!--<#=table.Name#>"-->!        
        <#} #>
</Biml>

Save that as 10_Demo.biml

The "trick" now is that in Visual Studio, you need to hold down the control key and select each Biml file and then right click and Generate SSIS Package(s)

Assorted Biml answers with Tier in them https://stackoverflow.com/search?tab=newest&q=user%3a181965%20%5bbiml%5d%20tier