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

Find ramblings

Tuesday, February 18, 2014

Biml - Execute SQL Server Agent Job

Biml - Execute SQL Server Agent Job

The Execute SQL Server Agent Job task allows you to run an agent job. It takes an ADO.NET connection and the name of the job to be executed. Unlike the backup database or check db task, this only allows for the specification of one job to executed.

<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_ExecuteSQLServerAgentJob">
            <Tasks>
                <ExecuteSqlAgentJob 
                    AgentJob="syspolicy_purge_history" 
                    ConnectionName="CM_ADO_DB" 
                    Name="ESAJ Run Jobs">
                </ExecuteSqlAgentJob>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with an ADO.NET connection manager and an Execute SQL Server Agent Job task which fires the syspolicy_purge_history job. This should exist on servers so the package should pass validation.

Execute SQL Server Agent Job

The task is a simple one. Don't let the check box fool you, it's a radio button in that you can only pick one job.

No comments: