BIML Template for creating SSIS packages

Template.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#=CallBimlScript("Connections.biml")#>
  <Packages>
    <Package Name="{{PACKAGE-NAME}}" ProtectionLevel="DontSaveSensitive" ConstraintMode="Linear">
      <Annotations>
        <Annotation AnnotationType ="Description">{{PACKAGE-NAME}}</Annotation>
      </Annotations>
      <Connections>
        <!-- BI connections -->
        <Connection ConnectionName="Staging"/>
        <Connection ConnectionName="Warehouse"/>
      </Connections>
      <Variables>
        <Variable DataType="Int16" Name="ETL_Run_ID">0</Variable>
      </Variables>

      <!-- Error logging -->
      <Events>
        <Event EventType="OnError" Name="{{PACKAGE-NAME}}_OnError" ConstraintMode="Linear">
          <Tasks>
            <!-- Audit - Log the job ended (Failure)-->
            <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_Update">
              <Annotations>
                <Annotation AnnotationType="Description">Updates the [Audit].[JobList] table with the failure</Annotation>
              </Annotations>
              <DirectInput>EXEC [Audit].[USP_JobLog] @Flag='F', @Package_Name = ?, @Job_Status = 'F', @ETL_Run_ID = ?</DirectInput>
              <Parameters>
                <Parameter Name="0" VariableName="System.PackageName" DataType="AnsiString" />
                <Parameter Name="1" VariableName="User.ETL_Run_ID" DataType="Int16" />
              </Parameters>
            </ExecuteSQL>
            <!-- Audit - Log the actual failure details -->
            <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_ErrorLog">
              <Annotations>
                <Annotation AnnotationType="Description">Insert error details into [Audit].[ErrorLog] table</Annotation>
              </Annotations>
              <DirectInput>EXEC [Audit].[USP_ErrorLog] @ETL_Run_ID = ?, @Error_Code = ?, @Error_Desc = ?, @Package_Name = ?, @Task_Name = ?</DirectInput>
              <Parameters>
                <Parameter Name="0" VariableName="User.ETL_Run_ID" DataType="Int16" />
                <Parameter Name="1" VariableName="System.ErrorCode" DataType="Int32" />
                <Parameter Name="2" VariableName="System.ErrorDescription" DataType="AnsiString" />
                <Parameter Name="3" VariableName="System.PackageName" DataType="AnsiString" />
                <Parameter Name="4" VariableName="System.SourceName" DataType="AnsiString" />
              </Parameters>
            </ExecuteSQL>
          </Tasks>
        </Event>
      </Events>

      <Tasks>

        <!-- Audit - Log the job start -->
        <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_Insert" ResultSet="SingleRow">
          <Annotations>
            <Annotation AnnotationType="Description">Inserts into [Audit].[JobLog] table for auditing of data load start time</Annotation>
          </Annotations>
          <DirectInput>EXEC [Audit].[USP_JobLog] @Flag='I', @Package_Name = ?, @Job_Status = 'R', @ETL_Run_ID = 0</DirectInput>
          <Parameters>
            <Parameter Name="0" VariableName="System.PackageName" DataType="AnsiString" />
          </Parameters>
          <Results>
            <Result VariableName="User.ETL_Run_ID" Name="0"></Result>
          </Results>
        </ExecuteSQL>

        <!-- Foreach Table -->
        <Container Name="DFT_{{TABLENAME}}" ConstraintMode="Linear">
          <Variables>
            <Variable DataType="Int32" Name="Total_Count">0</Variable>
            <Variable DataType="Int32" Name="Failed_Count">0</Variable>
          </Variables>
          <Tasks>
            <!-- Audit - Log the table load start -->
            <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_Insert_{{TABLENAME}}">
              <Annotations>
                <Annotation AnnotationType="Description">Inserts into [Audit].[RunTableRowCounts] table for auditing of table ETL start time</Annotation>
              </Annotations>
              <DirectInput>EXEC [Audit].[USP_RunTableRowCounts] @Flag = 'I', @ETL_Run_ID = ?, @Package_Name = ?, @Table_Name = ?, @Load_Status = 'R', @Success_Count = 0, @Failed_Count = 0</DirectInput>
              <Parameters>
                <Parameter Name="0" VariableName="User.ETL_Run_ID" DataType="Int16" />
                <Parameter Name="1" VariableName="System.PackageName" DataType="AnsiString" />
                <Parameter Name="2" VariableName="System.TaskName" DataType="AnsiString" />
              </Parameters>
            </ExecuteSQL>

            <!-- Data flow task - the actual loading logic -->
            <Dataflow Name="DFT_{{TABLENAME}}">
              <Transformations>
                <!-- Actual select statement - avoid select * like the plague! -->
                <OleDbSource ConnectionName="Source" Name="OLE_SRC_{{TABLENAME}}">
                  <DirectInput>Select 1 a;</DirectInput>
                </OleDbSource>
                <!-- Log the row count from the select to the total variable -->
                <RowCount VariableName="User.Total_Count" Name="CNT_Total_Count"></RowCount>
                <!-- Add the audit columns so we can track when\who loaded the data -->
                <DerivedColumns Name="DER_Audit_Columns">
                  <Columns>
                    <Column DataType="Int16" Name="ETL_Run_ID">@[User::ETL_Run_ID]</Column>
                    <Column DataType="AnsiString" Name="ETL_Created_By" Length="30">(DT_STR,30,1252)@[System::UserName]</Column>
                    <Column DataType="DateTime" Name="ETL_Created_Date">@[System::StartTime]</Column>
                  </Columns>
                </DerivedColumns>
                <!-- Write the data out -->
                <OleDbDestination ConnectionName="Staging" Name="OLE_DST_{{TABLENAME}}">
                  <ExternalTableOutput Table="CareFirst.Answers" />
                  <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow"/>
                </OleDbDestination>
                <!-- Count the number of rows we couldn't write out -->
                <RowCount VariableName="User.Failed_Count" Name="CNT_Failed_Count">
                  <InputPath OutputPathName="OLE_DST_{{TABLENAME}}.Error" />
                </RowCount>
              </Transformations>
            </Dataflow>

            <!-- Audit - Log the table load finished and the row counts (total and failed) -->
            <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_Update_{{TABLENAME}}">
              <Annotations>
                <Annotation AnnotationType="Description">Updates the [Audit].[RunTableRowCounts] table with the end time and the row count for both total select and failed inserts</Annotation>
              </Annotations>
              <DirectInput>EXEC [Audit].[USP_RunTableRowCounts] @Flag = 'U', @ETL_Run_ID = ?, @Package_Name = ?, @Table_Name = ?, @Load_Status = 'C', @Success_Count = ?, @Failed_Count = ?</DirectInput>
              <Parameters>
                <Parameter Name="0" VariableName="User.ETL_Run_ID" DataType="Int16" />
                <Parameter Name="1" VariableName="System.PackageName" DataType="AnsiString" />
                <Parameter Name="2" VariableName="System.TaskName" DataType="AnsiString" />
                <Parameter Name="3" VariableName="User.Total_Count" DataType="Int32" />
                <Parameter Name="4" VariableName="User.Failed_Count" DataType="Int32" />
              </Parameters>
            </ExecuteSQL>

          </Tasks>
        </Container>

        <!-- Audit - Log the job ended (Success)-->
        <ExecuteSQL ConnectionName="Warehouse" Name="EXEC_Audit_Update">
          <Annotations>
            <Annotation AnnotationType="Description">Updates the entry in the [Audit].[JobLog] table with the end time</Annotation>
          </Annotations>
          <DirectInput>EXEC [Audit].[USP_JobLog] @Flag='U', @Package_Name = ?, @Job_Status = 'C', @ETL_Run_ID = ?</DirectInput>
          <Parameters>
            <Parameter Name="0" VariableName="System.PackageName" DataType="AnsiString" />
            <Parameter Name="1" VariableName="User.ETL_Run_ID" DataType="Int16" />
          </Parameters>
        </ExecuteSQL>

      </Tasks>
    </Package>
  </Packages>
</Biml>