Get the SSIS Event Name in Script Task

Question

I am wanting to run a standard script task in package level events (OnError, OnPreExecute, OnPostExecute) for logging.

Ideally I would like to be able to just copy and paste the Script task, but it needs to know the Event that it is running inside of. Currently I pass that in as a use variable, but this means that I need to manually set the variable to store the name of the event. That's not too arduous, but it is easily forgotten. Thus my question is, is there a performant way to detect which event the script task is running within.

My suspicion is that this is not easy as essentially this is the same as finding out the name of the parent container, which turns out to be next to impossible. Another option is to be able to sniff the scope of standard event variables, such as 'EventHandlerStartTime', but I can't find a way to determine the scope of a variable from within the script task.

Any help is much appreciated.

Answer 1

Since @Mark mentioned Biml, I thought I'd take a moment to explode out the concept of using some very basic concepts to make this happen.

Assuming I had a pathetic logging table like

CREATE TABLE 
    dbo.DoWhat
(
    PackageName nvarchar(150) NULL
,   ParentContainerGUID varchar(38) NULL
,   SourceDescription nvarchar(1000) NULL
,   SourceName nvarchar(150) NULL
,   SourceParentGUID varchar(38) NULL
,   EventName varchar(20) NULL
);

then I could define a "function" in a file called inc_events.biml

What that says, this file expects a parameter of type string that will populate the eventName variable. I then use a classic ASP style syntax for subbing in that value <#=eventName#>

So, this biml will create an Event. That event has a Variable named WhereAmI that is scoped to the Event. I then have an Execute SQL Task in there that does an insert into the logging table passing along System variables and my local variable name.

You're looking to use a Script Task so you'd replace the Execute SQL Task but the concept is the same. There's a something in there that's going to use our local variable that had the event's name assigned to it.

<#@ property name="eventName" type="String" #>
                <Event EventType="<#=eventName#>" ConstraintMode="Linear" Name="<#=eventName#>">
                    <Variables>
                        <Variable DataType="String" Name="WhereAmI"><#=eventName#></Variable>
                    </Variables>
                    <Tasks>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Log notes">
                            <VariableInput VariableName="User.QueryLog"></VariableInput>
                            <Parameters>
                                <Parameter DataType="String" VariableName="System.PackageName" Name="0" />
                                <Parameter DataType="AnsiString" VariableName="System.ParentContainerGUID" Name="1" DataTypeCodeOverride="129" />
                                <Parameter DataType="String" VariableName="System.SourceDescription" Name="2" />
                                <Parameter DataType="String" VariableName="System.SourceName" Name="3" />
                                <Parameter DataType="AnsiString" VariableName="System.SourceParentGUID" Name="4" DataTypeCodeOverride="129" />
                                <Parameter DataType="AnsiString" VariableName="User.WhereAmI" Name="5" DataTypeCodeOverride="129" />
                            </Parameters>
                        </ExecuteSQL>
                    </Tasks>
                </Event>

Using the include file

I add a second Biml file, so_27378254_inc.biml to my project. This one is going to demonstrate how we use the function/include file.

I define an OLE DB database connection in Connections collection called tempdb

In my Packages collection, I define a new package called so_27378254_inc. It has a Variable, QueryLog which just contains the format for an insert statement into my log table.

In the package's Events collection, I then invoke CallBimlScript 3 times, one for each Event Handler I want to add at the package level scope.

Into the Tasks collection, I add a Sequence Container just so I can get some variety in my log.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="tempdb" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_27378254_inc">
            <Variables>
                <Variable DataType="String" Name="QueryLog">
                    <![CDATA[INSERT INTO  
    dbo.DoWhat
(
    PackageName
,   ParentContainerGUID
,   SourceDescription
,   SourceName
,   SourceParentGUID
,   EventName
)
SELECT
    ? /* AS PackageName */
,   ? /* AS ParentContainerGUID */
,   ? /* AS SourceDescription */
,   ? /* AS SourceName */
,   ? /* AS SourceParentGUID */
,   ? /* AS EventName */
;]]>
                </Variable>
            </Variables>
            <Events>
<#=CallBimlScript("inc_events.biml", "OnError")#>
<#=CallBimlScript("inc_events.biml", "OnPreExecute")#>
<#=CallBimlScript("inc_events.biml", "OnPostExecute")#>
</Events>
            <Tasks>
                <Container Name="SEQC Container on Control Flow" ConstraintMode="Linear">

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

So, what do you do with it? You right click on the so_27378254_inc.biml file and whoosh, a new package is generated.

How the heck do I sell this?

You mention in your comments, and rightfully so, concerns over anything that would "add friction to adoption".

My counter to that is

1. BIDS Helper is free and if you're not using it, you're not being as effective in your delivery as you could be. Especially pre-2012, there's just too many sharp edges in the product.
2. Using an approach like this will get everyone to a consistent starting point, without some of the hassles that come with template packages (IDs are not automatically updated until 2012)
3. You don't have to go all in on the Biml approach. Assuming you're the architect/lead developer, use it to define best practices. Everyone has logging turned on, apply configurations, get the standard connections defined, etc. All people have to do is instead of creating new SSIS, they right click and generate the template package and then rename it to something appropriate and they're off the races worrying about data flows and file acquisition, etc.