Transformer targeting multiple SSIS versions

gravatar

Peter Avenant

Just a quick snippet showing how you can selectively transform your output for different versions of SSIS. We use transformers extensively and our framework is in use by multiple customers on different versions of SQL Server. The goal here is to have a single version of your framework and we also use this to switch between versions of our custom components.

published 08.11.14

last updated 08.11.14


Share

Tags

  • Transformer
                            


<#@ 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>
                        
You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Emil

7:49pm 02.04.15

I am using Mist 4.0 and am attempting to use this in a live script. However, as soon as I attempt to use the CompilerSettings object in my code, I receive this error: "BimlScript code produced an exception: Object reference not set to an instance of an object. at BimlScriptCode in ."

I haven't been able to find anything on varigence.com documenting CompilerSettings besides this snippet and the forum thread at http://www.varigence.com/Forums?threadID=234. David's solution there doesn't really look anything at all like what you've got in your snippet here.

Is CompilerSettings not available in live scripts or is there some setting I'm missing? I intend to keep playing with it, but any insight you can offer is greatly appreciated.

gravatar

DavidS

7:53pm 02.25.16

Sorry for the late reply Emil, but I found your question while searching for the same thing.The following should work in non-transformer BimlScripts. <#=RootNode.BuildSettings.SsisVersion #>