Skip to main content

Dynamic BIML Module

In this snippet, I created 2 BIML scripts.

  1. "DynamicModule" that accept 2 parameters (TableName, AuditTableName). This script create 2Execute SQL tasks one for procedure execution based on Tablename parameter and other is AuditTableName Insert.

  2. "MainBIML" that gather metadata for tables to process and call DynamicModule BIML by passing parameters to create final package.

You can use this dynamic module in different BIML scripts within visual studio project. (Ex. FactTableProcess or DimensionProcess)

<!--========================================================================================-->
<!--========================================================================================-->
<#@ property name="TableName" type="String" #>
<#@ property name="AuditTableName" type="String" #>
<#@ template language ="C#" hostspecific ="true"#>
<#@ import namespace ="System.Data"#>
<# var Connection =@"Provider=SQLNCLI11.1;Data Source=xxx;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=xxx";#>
<# var sSQLCWS ="SELECT SourceTableName,ProcedureName,MAX(Level) FROM METADATA.TVF_GetTableTrackBack ('" + TableName + "') GROUP BY SourceTableName,procedurename ORDER BY MAX(level) DESC,SourceTableName;";#>
<#DataTable CWSPackage = ExternalDataAccess.GetDataTable(Connection,sSQLCWS);#>
<!--========================================================================================-->
<!--========================================================================================-->

<# foreach (DataRow row in CWSPackage.Rows) { #>
<Container Name="<#=row["SourceTableName"]#>" ConstraintMode ="Linear">
  <Variables>
    <Variable Name="<#=row["SourceTableName"]#>_ExecutionStatus"  DataType ="Int32">0</Variable>
    <Variable Name="<#=row["SourceTableName"]#>_ReturnMessage"    DataType ="String">Success</Variable>
    <Variable Name="<#=row["SourceTableName"]#>_DFTStartDate"     DataType="DateTime">01/01/2015</Variable>
  </Variables>
  <Tasks>
    <Expression Expression="@[User::<#=row["SourceTableName"]#>_DFTStartDate] =  GETDATE()" Name="Capture <#=row["SourceTableName"]#> Table StartTime"></Expression>
    <!--========================================================================================-->
    <!--========================================================================================-->
    <ExecuteSQL ConnectionName="Target" ResultSet= "None" Name="Process <#=row["SourceTableName"]#>">
      <DirectInput> Execute [dbo].[<#=row["ProcedureName"]#>] @p_sequence= 1, @p_job_name = NULL, @p_task_name = NULL, @p_job_id = 0, @p_task_id = 0, @p_return_msg =? OUTPUT, @p_status =  ? OUTPUT ; </DirectInput>
      <Parameters>
        <Parameter Name="0" Length="-1" Direction="Output" DataType="String" VariableName="User.<#=row["SourceTableName"]#>_ReturnMessage"></Parameter>
        <Parameter Name="1" Length="-1" DataType="Int32" Direction="Output" VariableName="User.<#=row["SourceTableName"]#>_ExecutionStatus"></Parameter>
      </Parameters>
    </ExecuteSQL>
    <!--========================================================================================-->
    <!--========================================================================================-->
    <ExecuteSQL ConnectionName ="Target" ResultSet="None"  Name="Log <#=row["SourceTableName"]#> Audit ">
      <DirectInput>
         INSERT INTO [Preload].[<#=AuditTableName#>]
         ([LogSK]
         ,[TableName]
         ,[ExecutionMessage]
         ,[Status]
         ,[StartDate]
         ,[EndDate])
         VALUES
         (?
         ,'<#=row["SourceTableName"]#>'
         ,?
         ,?
         ,?
         ,GETDATE())
      </DirectInput>
      <Parameters>
        <Parameter Name="0" DataType="Int32"	  Direction="Input" VariableName  ="User.LogSK"></Parameter>
        <Parameter Name="1" DataType="String"	  Direction="Input" VariableName  ="User.<#=row["SourceTableName"]#>_ReturnMessage"></Parameter>
        <Parameter Name="2" DataType="Int32"    Direction="Input" VariableName  ="User.<#=row["SourceTableName"]#>_ExecutionStatus"></Parameter>
        <Parameter Name="3" DataType="DateTime" Direction="Input" VariableName  ="User.<#=row["SourceTableName"]#>_DFTStartDate"></Parameter>
      </Parameters>
    </ExecuteSQL>
    <!--========================================================================================-->
    <!--========================================================================================-->
  </Tasks>
</Container>
<#}#>

<!--MainBIML Sample shows how to call DynamicModule-->
<!--========================================================================================-->
<!--============================================================-->
<!--============================================================-->
<# var Connection =@"Provider=SQLNCLI11.1;Data Source=xxx;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=xxx";#>
<# var sSQLCWS ="SELECT  ST.TableName AS SourceTableName FROM Metadata.SourceTable AS ST INNER JOIN Metadata.TableType AS TT ON TT.TableTypeID = ST.TableTypeID WHERE TT.TableTypeName = N'Fact' and ST.TableName not in ('FactCurrencyExchangeRate','FactWorkerMonthlyHeadcountDetail');";#>
<# var sSQLCWS1 ="SELECT  ST.TableName AS SourceTableName FROM Metadata.SourceTable AS ST INNER JOIN Metadata.TableType AS TT ON TT.TableTypeID = ST.TableTypeID WHERE TT.TableTypeName = N'CustomP' OR ST.TableName = 'FactWorkerMonthlyHeadcountDetail';";#>
<# DataTable CWSPackage = ExternalDataAccess.GetDataTable(Connection,sSQLCWS);#>
<# DataTable CWSPackage1 = ExternalDataAccess.GetDataTable(Connection,sSQLCWS1);#>


<!--============================================================-->
<!--============================================================-->
<Variables>
  <Variable Name="LogSK"					DataType ="Int32" EvaluateAsExpression="true">@[$Package::LogSK]</Variable>
  <Variable Name="TargetServer" DataType ="String" EvaluateAsExpression="true">@[$Package::TargetServer]</Variable>
  <Variable Name="ProductName" DataType ="String">DW</Variable>
  <Variable Name="TargetDB"     DataType ="String" EvaluateAsExpression="true">@[$Package::TargetDB]</Variable>
  <Variable Name="ExecutionStatus"  DataType ="Int32">0</Variable>
  <Variable Name="ReturnMessage"    DataType ="String">Success</Variable>
</Variables>
<!--============================================================-->
<!--============================================================-->
<Tasks>
  <!--============================================================-->
  <!--============================================================-->
  <Container Name="Process CurrencyExchangeRates" ConstraintMode = "Parallel">
    <Tasks>
      <!--============================================================-->
      <!--============================================================-->
      <Container Name="CurrencyExchangeRates Process" ConstraintMode ="Linear">
        <Tasks>
          <#=CallBimlScript("DynamicModule.biml", "CurrencyExchangeRate","AuditUtilFactTableLog")#>
        </Tasks>
      </Container>
    </Tasks>
  </Container>