Biml for CozyRock

Question

I tried out the bimlStudio and made a BIML file Included was a custom component of Cozyrock tabledifference

But the dtsx that was created was not what that component expected.

It expects

<arrayElements>
  <arrayElement datatype="...">
  </arrayElement>
</arrayElements>

But the bimlStudio made

<inputColumns>
  <inputColumn>
  </inputColumn>
</inputColumns>

Question: - can I make the tag arrayElements in Biml?

if not, can I output custom tags via some programming something like

<# for each>
arrayElement arrayElement <# end>

but how do I insert the custom tags into the dtsx that comes out of the biml?

What do you mean by custom tags? You can only use the included XML syntax. If you want to loop you need to write C# code inside ur BIML. — SqlKindaGuy

SqlKindaGuy SqlKindaGuy · Accepted Answer · 2017-08-24T12:12:05

Im not sure what you exactly mean, but you can do stuff like this:

<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Threading.Tasks" #>
<#@ import namespace="System.Linq" #>
<#@ template language="C#" hostspecific="True" #>
<#@ assembly name="\\XXXXXXXX\Build\DLL\MetaFrameWork.dll" #>
<#@ import namespace="MetaFrameWork"#>
<#@ property name="pDWDatabaseName" type="String" #>
<#@ property name="pDatabaseName" type="String" #>
<#@ property name="pSolutionAreaName" type="String" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#  
string logConnectionString = "Provider=XXXXX;Server=XXXXXX;Integrated 
Security=SSPI;Initial Catalog=Utility";
string DWDatabaseName = pDWDatabaseName;
string DatabaseName = pDatabaseName.ToUpper();
string SolutionAreaName = pSolutionAreaName.ToUpper();
string MasterPackageName = "MASTERPACKAGE_LOAD_DIM_" + SolutionAreaName + 
"_FROM_" + DWDatabaseName.ToUpper() + "_TO_" + DatabaseName;
string PackageName;

var metadataSelect = Metaframework.GetDimFullLoad(DatabaseName);
List<string> packagescreated = new List<string>();#>

<Projects>
<PackageProject Name="SSIS_DM_<#=SolutionAreaName #>_Dim">
    <Packages>
        <# foreach (var tables in metadataSelect) {
            PackageName = 
"LOAD_DIM_"+tables.DatabaseName.ToUpper()+"_"+tables.EntityName.ToUpper();
            packagescreated.Add(PackageName);#>
            <Package PackageName="<#=PackageName #>" />
        <#} #>
        <Package PackageName="<#=MasterPackageName #>" />
    </Packages>
</PackageProject>
</Projects>
<Connections>
<Connection Name="UTILITY" ConnectionString="<#=logConnectionString #>" 
CreateInProject="true" />
<#=CallBimlScript("..\\..\\Connections\\ProjectConnections.biml",DatabaseNam
e,SolutionAreaName) #>
<#=CallBimlScript("..\\..\\Connections\\ProjectConnections.biml",DWDatabaseN
ame,SolutionAreaName) #>
</Connections>
<Packages>
<# foreach (var tables in metadataSelect) { 
    PackageName = 
"LOAD_DIM_"+tables.DatabaseName.ToUpper()+"_"+tables.EntityName.ToUpper(); 
#>
<Package Name="<#=PackageName #>" ConstraintMode="Linear">
    <#=CallBimlScript("..\\..\\Log\\StaticVariables.biml", "Log") #>
    <Tasks>
        <Container Name="SEQ  DIM <#=tables.EntityName#>" 
ConstraintMode="Linear">
            <Tasks>
                <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogStart.biml", 
PackageName) #>
                <ExecuteSQL Name="SQL TRUNCATE <#=tables.EntityName #>"  
ConnectionName="<#=tables.DatabaseName#>" ResultSet="None"> 
                    <DirectInput>   
                      TRUNCATE TABLE [<#=tables.SchemaTypeName #>].
[<#=tables.EntityName #>]                                       
                    </DirectInput>
                    <Parameters>
                        <Parameter Name="@ExecutionKey" 
VariableName="User.ExecutionKey" DataType="Int32" />
                    </Parameters>
                </ExecuteSQL>
                <Dataflow Name="DFT 
LOAD_DIM_<#=tables.DatabaseName#>_<#=tables.EntityName #>">
                    <Transformations>
                        <OleDbSource ConnectionName="<#=DWDatabaseName #>" 
Name="OLE_SRC <#=tables.EntityName #>">
                            <DirectInput>                                   
                            <# var columnsmetadata = 
Metaframework.GetInputToDimFullLoad(tables.EntityID); 
                            string columns = string.Join("],[", 
columnsmetadata.Select(c => c.S_ColumnName));

                            string database = 
Metaframework.GetInputToDimFullLoad(tables.EntityID).Select(t => 
t.S_DatabaseName).Distinct().First();

                            string loadtype = 
Metaframework.GetInputToDimFullLoad(tables.EntityID).Select(t => 
t.D_LoadTypeName).Distinct().First();

                             #>
                            <#if(loadtype.ToLower().Equals("t2")) { #>
                            SELECT [<#=columns  #>],[GyldigFra],[GyldigTil],
[ErAktuel] FROM [<#=database #>].[out].[<#=tables.EntityName#>] with 
(nolock)
                                <#}
                                  else {
                                      #>    SELECT [<#=columns  #>] FROM 
[<#=database #>].[out].[<#=tables.EntityName#>] with (nolock)
                                 <# }#>
                            </DirectInput>

                      </OleDbSource>
                        <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogDataFlowDerivedColumns.biml") #>
                        <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogDataFlowRowCount.biml") #>
                        <OleDbDestination ConnectionName="<#=tables.DatabaseName#>" Name="OLE_DST <#=tables.SchemaTypeName #>_<#=tables.EntityName#>">
                            <ExternalTableOutput Table="<#=tables.SchemaTypeName #>.<#=tables.EntityName #>">                               
                            </ExternalTableOutput>                                  
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>                     
                <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogEnd.biml") #>
            </Tasks>
        </Container>
    </Tasks>
</Package>
<#} #>
<Package Name="<#=MasterPackageName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
<#=CallBimlScript("..\\..\\Log\\StaticVariables.biml", "Log") #>
<Tasks>
    <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogStart.biml", MasterPackageName) #>
    <Container Name="SEQ Run packages in parallel" ConstraintMode="Parallel">
        <Tasks>      
        <# foreach (string package in packagescreated) { #>
             <ExecutePackage Name="EPT_<#=package#>">
                <ExternalProjectPackage Package="<#=package#>.dtsx" ></ExternalProjectPackage>
            </ExecutePackage>
        <# } #>
        </Tasks>
    </Container>
    <#=CallBimlScript("..\\..\\Log\\ETL_ExecutionLogEnd.biml") #>
    </Tasks>
</Package>
</Packages>
</Biml>

Biml for CozyRock

1 Answers