A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, March 11, 2014

Biml - Notify Operator Task

Biml - Notify Operator Task

The maintenance task Notify Operator contacts the pre-defined operators. It requires an ADO.NET connection manager to work. Specify the operator, subject and message contents and away you go.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_NotifyOperator">
            <Variables>
                <Variable Name="OperatorName" DataType="String">Ops</Variable>
                <Variable Name="Subject" DataType="String">Look here</Variable>
                <Variable Name="Message" DataType="String">Things have gone south</Variable>
                <Variable Name="Query" DataType="String" EvaluateAsExpression="true">"
DECLARE @OpName sysname = 'Ops';

IF NOT EXISTS
(
    SELECT * 
    FROM msdb.dbo.sysoperators AS SO 
    WHERE SO.name = @OpName
)
EXECUTE msdb.dbo.sp_add_operator 
    @name = @OpName
,   @enabled = 1
,   @pager_days = 0
,   @email_address = N'spam@billfellows.net';
                "</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL 
                    ConnectionName="CM_ADO_DB" 
                    Name="SQL Create Operator">
                    <VariableInput VariableName="User.Query" />
                </ExecuteSQL>
                
                <NotifyOperator 
                    ConnectionName="CM_ADO_DB" 
                    Name="NO Ack">
                    <OperatorsToNotify>
                        <Operator>Ops</Operator>
                    </OperatorsToNotify>
                    <Subject>Ack</Subject>
                    <Message>Body of the notification</Message>
                    <Expressions>
                        <Expression PropertyName="Subject">@[User::Subject]</Expression>
                        <!--<Expression PropertyName="OperatorName">@[User::Subject]</Expression>-->
                        <Expression PropertyName="Message">@[User::Message]</Expression>
                    </Expressions>
                </NotifyOperator>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with an Execute SQL Task to ensure our Operator exists and then an actual Notify Operator Task is dropped into our Control Flow.

NO Ack

This is our Notify Operator task and it acknowledges the errors on the server.

Expression

I could figure out how to configure the message and the subject but couldn't figure out how to set the Operator name via expression.

No comments: