MIST – Transformers

(MIST Only Feature)

Many times when I was getting stared on the MIST platform I was getting confused at multiple occasions where I was trying out the concept of transformers out in the BIDS Helper.

So to clear out the air, this is only an MIST platform specific feature.

Now moving on to the concepts:

Transformer: This is a piece of code which we write up in MIST, which run post build process and scans over our entire BIML structure and where ever the instructed node type is matched based on the “MergeMode” directive the specified script is appended/replace or more…

This is a very powerful feature which I will try to scratch the surface in this article, exhibiting its utility.

Scenario:

I have built a package using MIST/BIML which generates a create table via “ExecuteSqlTask” for each table. And just to increase the complexity that the count of my tables is >10. Now I am presented with a business requirement that the SSIS must be error handling capable, so that “OnError” we can log which component caused the error.

Now I have a situation where manually I can open the package and create an “OnError” event handler manually for >10 components (sound tedious) , or in the BIML script I copy and past the following script >10 times at relevant places where error handling is required or amend the actual package generating script.

<Events>
		<Event EventType="OnError" Name="SayHello">
			<Tasks>
				<ExecuteSQL Name="Error_<#=TargetNode.Name #>" ConnectionName="AdventureWorksLT2012">
					<DirectInput>
						SELECT 1;
                    </DirectInput>
				</ExecuteSQL>	
	        </Tasks>
	    </Event>
	</Events>

(Please ignore my DirectInput in the example, as its for representational purposes only, instead it should be of an insert/store procedure execution for error logging)

So in all the options above the, error handling code is included my coding logic, where as it would be nice if some thing could detect post build that where ever I have an execute SQL task container attach an standard “OnError” handler to it.

So stopping my talks, and getting down to code:

Transformer code: (PackageErrorLogging.bimlt)

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

<ExecuteSQL>
	<Events>
		<Event EventType="OnError" Name="SayHello">
			<Tasks>
				<ExecuteSQL Name="Error_<#=TargetNode.Name #>" ConnectionName="AdventureWorksLT2012">
					<DirectInput>
						SELECT 1;
                    </DirectInput>
				</ExecuteSQL>	
	        </Tasks>
	    </Event>
	</Events>
</ExecuteSQL>

Now how do I instruct MIST to execute post build:

They have now introduced some thing called as “Frameworks”, just add one of the MIST Frameworks and specify the code path to your transfoemr and you are done, setting up the build behaviour, which you can control by Ribbon interface of the MIST also.

Framework: (ErrorHandlerBuild.bimlfx)

<FrameworkSettings RootItemName="RootNode" TargetItemName="TargetNode" xmlns="http://schemas.varigence.com/FlowModel.xsd">
    <TransformerBimlScripts>
        <TransformerBimlScript TransformerBimlScriptFilePath="C:\Users\Kostya\Documents\Mist\Sales_DV\addedBiml\Transformers\PackageErrorLogging.bimlt" />
    </TransformerBimlScripts>
</FrameworkSettings>

And the build of the code can be instructed from the ribbon whether to include transformer or not…

And the results can any time be looked on to your generated SSIS packages:

More additional help on Transformers is available at the following links:

BimlScript Transformers Primer
Transformer targeting multiple SSIS versions
Biml Transformers – Webinar (YouTube)

Hope it helps…

Downloads