ETL Architecture Smart Examples & Snippets (2 of N)

Following the previous post let’s focus on the SQL Agent jobs definition & PROCS.

It’s very common practice deploying SSIS pkg and setup sql agent job to invoke them; in that case you can define the execution credential of the package itself, managing the scheduling and logging the sqlagent execution, so much better the invoking a pkg in SQL management studio 😉

Normal Mode and Override Mode

It’s of great advantage when defining a dtsx logic thinking in term of normal mode execution and override mode, let me explain that. SSIS by its nature is part of ETL (or ELT) processing. This means you usually have some parameters against which data is processed (loaded, cleansed, transformed etc…); this parameter is usually a date (or an int representing a date ex 20140127). A normal mode execution would check the conditions for which new data is ready to be processed in the source and not yet loaded; an example would be a dtsx importing data from a source system during the moth: the pkg is scheduled to run each day; it checks the source and looking for new data (not loaded in the target yet). An override mode on the other side it’s very useful in case some data loading was missing or more likely some previous load needs to be reprocessed (without complicating too much the job logic with weird checks)

A general core can be thought using the following sql snippets:

  1. SQLAgentJob_XYZ_NormalMode.sql
  2. SQLAgentJob_XYZ_OverrideMode.sql
  3. PROC_uspXYZ_OverrideMode.sql
  4. AddAgentJobSchedules.sql

Given the XYZ.dtsx package we can define it as:

Quite easy, just notice that the core logic is contained in the SEQC and the SQL Logs are responsible to track the pkg execution in a logging table or status table s we can use it to coordinate with other external processed.

The pkg defines the following var list (in general):

Let’s check the most relevant:

MonthEnd = the date used to checks the source and load data (if any) in the target. This can be a compueted value (we can define some SCR to calculate it SEQC Housekeeping Pre Tasks based on current GetDate() for example ) or we can use the override mode to define a specific value for it, in this way we can reuse the ‘normal’ logic to load the data as it was a normal mode execution, overriding the value of the MonthEnd with what we need

TargetImportOverrideFlag = Y or N: to run the dtsx in the Override/Normal modality, it the override mode the SourceReady & TargetReady can be set Y without further checks so the logic would think the data need to be loaded (usually you’d define a SQL task to delete * from Target where MonthEnd = @ MonthEnd before data reloading, in normal mode would do nothing of course, while in override would clear the data to reload)


The general script is here: it’s nothing special, just use it to deploy the sql agent job to run the XYZ pkg against PRODSERVER

Note the usage of

“Package.Variables[User::TargetImportOverrideFlag].Properties[Value]”;N the variable is the one to tell to the pkg to behave and load in normal mode, that means usually to calculate the value of the TargetMonthEnd, the values of SourceReady and TargetReady (as in normal mode we don’t want to reloaded data the was previously loaded in the target)

	Artifact Deploy.SQLAgentJob_#XYZ#_NormalMode.sql

USE [msdb]

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'MYPKGS'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'#XYZ#_NormalMode')
EXEC msdb.dbo.sp_delete_job @job_name=N'#XYZ#_NormalMode', @delete_unused_schedule=1

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'#XYZ#_NormalMode',
		@description=N'... #XYZ# ...
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'DOMAIN\ACCOUNT', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Execution of #XYZ# in Normal Mode]    Script Date: 11/05/2013 12:14:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execution of #XYZ# in Normal Mode',
		@os_run_priority=0, @subsystem=N'SSIS',
		@command=N'/SQL "\FRDB_rFrame Packages\#XYZ#" /SERVER PRODSERVER /CHECKPOINTING OFF /SET "\Package.Variables[User::TargetImportOverrideFlag].Properties[Value]";N /REPORTING E /CONNECTION "Config_Conn";"\"Data Source=PRODSERVER;Initial Catalog=ETL_Logging;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=#XYZ#',
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave


The difference with SQLAgentJob_XYZ_OverrideMode.sql is just here:

/SET “Package.Variables[User::TargetImportOverrideFlag].Properties[Value]”;Y


This is a PROC to invoke the agent job in override mode, passing the necessary parameters. It’s much better having a PROC for such a purpose, so if the pkg needs other input parameters to run, they can be defined here and use the SQL security to allow only a group of users to reload data

	Arfifact: Deploy.PROC_usp#XYZ#_OverrideMode.sql

USE [ETL_Logging]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp#XYZ#_OverrideMode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp#XYZ#_OverrideMode]



CREATE PROC [dbo].[usp#XYZ#_OverrideMode]
  @TargetMonthEnd INT,
, @RunAgentJob CHAR (1) = 'Y'
-- Procedure: Deploy.usp#XYZ#_OverrideMode
-- Author:    MarioA
-- Created:   2013/11/06
-- Purpose: ...
-- Revision History

	DECLARE @ConfigFilter VARCHAR(255) = 'Config_#XYZ#';
	DECLARE @AgentJob VARCHAR(255) = '#XYZ#_OverrideMode';
	DECLARE @InvalidFileFlag BIT = 0;
	DECLARE @Parametervalues VARCHAR(8000)= '';

	IF  @RunAgentJob = 'Y' AND @File NOT IN ('ALL')
	SET @InvalidFileFlag = 1;

	SELECT @Parametervalues = '@File:'+ @File + ' @TargetMonthEnd:' + CONVERT(NVARCHAR(255), @TargetMonthEnd);

	IF @InvalidFileFlag = 0

		UPDATE ETL.SSISConfigurations
		SET ConfiguredValue = CONVERT(NVARCHAR(255), @TargetMonthEnd)
		WHERE ConfigurationFilter = @ConfigFilter AND PackagePath = '\Package.Variables[User::TargetMonthEnd].Properties[Value]';

		IF @RunAgentJob = 'Y'
			EXEC MSDB.dbo.sp_start_job @Job_Name = @AgentJob;

		PRINT '>The agent job ' + @AgentJob + ' has been launched with the supplied parameters values: '+ @Parametervalues;
		PRINT 'Please check the Job Activity Monitor to see the progress of the agent job.';
		PRINT '>The supplied parameters values: '+ @Parametervalues +' are invalid for this stored procedure. Please check and try again.'


A more relaisitic example is

DECLARE @TargetMonthEnd int = 20131231
DECLARE @ExtractID int = 123
DECLARE @File varchar(255) ='H1'


DECLARE @ConfigFilter VARCHAR(255) = 'Config_XYZ';

IF @ExtractID <> -1  AND @TargetMonthEnd<>0

-- Run the
EXECUTE [dbo].uspXYZ_OverrideMode

In his example we have ExtractID in the pkg (it might be an ID coming from another source, a PERISTENT source with data snapshots for example.


It’s the snippet to define the scheduling for the NormalMode]

	Artifact Deploy.Deploy.AddAgentJobSchedules.sql

USE msdb;

DECLARE @name_of_agent_job nvarchar(128) = '#XYZ#_NormalMode';
DECLARE @activestarttime int = 73000  -- start time

IF  EXISTS (SELECT * FROM dbo.sysschedules WHERE name = @name_of_agent_job)
	EXEC dbo.sp_delete_schedule @schedule_name = @name_of_agent_job, @force_delete=1;

EXEC dbo.sp_add_jobschedule @job_name=@name_of_agent_job, @name=@name_of_agent_job,
	@freq_type=4, -- 4 daily

Please share your thoughts:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s