Transformer Targeting Multiple SSIS Versions

A 'LocalMerge' Package transformer that gives every targeted package a standard execution-logging pattern: three User variables ('ExecutionID', 'ParentExecutionID', 'ParentSourceGUID') with parent-inheritance bindings, an 'OnPreExecute' event handler that calls 'ssis.LogExecution' inside a 'ForLoop' to obtain a server-side 'ExecutionID', and a re-entry guard that skips packages whose 'OnPreExecute' already exists. The transformer first checks 'TargetNode.Events["OnPreExecute"]' and reports a 'ValidationReporter.Report' message before returning if the pattern is already applied.

The C# code-nuggets switch behavior on 'CompilerSettings["SsisVersion"]'. On SSIS 2012 the 'ServerExecutionID' is sourced from the System namespace and bound to 'System.ServerExecutionID'; on earlier versions it is declared in the User namespace because the system variable does not exist. Under SSIS 2008 R2 the transformer also emits a 'SqlServerLogProvider', a 'LogEvents' collection (OnError, OnPreExecute, OnPostExecute, OnWarning), and the matching 'ssislog' connection reference, which 2012 does not need because catalog logging replaces it. Adjust the table/stored-procedure names and the 'ssislog' connection to match your own logging framework.

<#@ target mergemode="LocalMerge"  type="Package" #>

<# 	if(TargetNode.Events["OnPreExecute"] != null) {
    ValidationReporter.Report(Severity.Message, "{0} already has the pattern applied.", TargetNode.Name);
    return "";} #>
<Package>
  <Variables>
    <Variable Name="ExecutionID" DataType="Int32" Namespace="User">0</Variable>
    <Variable Name="ParentExecutionID" DataType="Int32" Namespace="User" InheritFromPackageParentConfigurationString="User::ExecutionID">-1</Variable>
    <Variable Name="ParentSourceGUID" DataType="String" Namespace="User" InheritFromPackageParentConfigurationString="System::TaskID" />
    <# 	if ((SsisVersion)CompilerSettings["SsisVersion"] == SsisVersion.Ssis2012){ #>
    <Variable Name="ServerExecutionID" DataType="Int64" Namespace="System">-1</Variable>
    <# } else { #>
    <Variable Name="ServerExecutionID" DataType="Int64" Namespace="User">-1</Variable>
    <#	} #>
  </Variables>
  <Events>
    <Event Name="OnPreExecute" EventType="OnPreExecute" ConstraintMode="Linear">
      <Tasks>
        <ForLoop Name="FRL - ExecutionID" ConstraintMode="Parallel" >
          <LoopTestExpression>@[User::ExecutionID]==0</LoopTestExpression>
          <Tasks>
            <ExecuteSQL Name="SQL - Log Execution" ConnectionName="BIMP" ResultSet="None">
              <DirectInput>EXEC [ssis].[LogExecution] @ExecutionGUID, @SourceGUID, @PackageName, @ParentSourceGUID, @ParentExecutionID, @ServerExecutionID, @ExecutionID OUTPUT</DirectInput>
              <Parameters>
                <Parameter Name="@ExecutionGUID" Direction="Input" DataType="String" VariableName="System.ExecutionInstanceGUID"/>
                <Parameter Name="@SourceGUID" Direction="Input" DataType="String" VariableName="System.TaskID"/>
                <Parameter Name="@PackageName" Direction="Input" DataType="String" VariableName="System.PackageName"/>
                <Parameter Name="@ParentSourceGUID" Direction="Input" DataType="String" VariableName="User.ParentSourceGUID"/>
                <Parameter Name="@ParentExecutionID" Direction="Input" DataType="Int32" VariableName="User.ParentExecutionID"/>
                <# 	if ((SsisVersion)CompilerSettings["SsisVersion"] == SsisVersion.Ssis2012){ #>
                <Parameter Name="@ServerExecutionID" Direction="Input" DataType="Int64" VariableName="System.ServerExecutionID"/>
                <# } else { #>
                <Parameter Name="@ServerExecutionID" Direction="Input" DataType="Int64" VariableName="User.ServerExecutionID"/>
                <#	} #>
                <Parameter Name="@ExecutionID" Direction="Output" DataType="Int32" VariableName="User.ExecutionID"/>
              </Parameters>
            </ExecuteSQL>
          </Tasks>
        </ForLoop>
      </Tasks>
    </Event>
  </Events>
  <# 	if ((SsisVersion)CompilerSettings["SsisVersion"] == SsisVersion.Ssis2008R2){ #>
  <LogEvents>
    <LogEvent EventName="OnError" />
    <LogEvent EventName="OnPostExecute" />
    <LogEvent EventName="OnPreExecute" />
    <LogEvent EventName="OnWarning" />
  </LogEvents>
  <LogProviders>
    <SqlServerLogProvider Name="SSIS log provider for SQL Server" ConnectionName="ssislog" />
  </LogProviders>
  <Connections>
    <Connection ConnectionName="ssislog" />
  </Connections>
  <#	} #>
</Package>