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:
Post a Comment