0
Shares
0
0
0

En ocasiones nos vemos en la necesidad de diseñar un paquete “Orquestador” en el que a través de tareas Execute package task establecemos un orden de ejecución. De esta manera nuestro paquete Orquestador será el encargado de ir ejecutando los paquetes contenidos en un orden. Pero puede ocurrir que en dicha ejecución se produzca un fallo, por ejemplo, por pérdida de conexión con el servidor, por lo que aunque nuestro job esté configurado para realizar reintentos el paquete Orquestador volvería a ejecutarse desde el inicio. ¿Y si en vez de ejecutarse desde un inicio pudiéramos ir registrando los paquetes del Orquestador que se han ejecutado correctamente para que en el reintento ejecute desde el paquete donde se quedó?

El objetivo de esta publicación es ver cómo configurar los Orquestadores para realizar reintentos sin tener que relanzar el Orquestador completo y que sólo se lancen los paquetes pendientes de ser ejecutados.

Para ello, debemos preparar nuestra BBDD y nuestro paquete Orquestador. Vamos a ver a continuación los pasos que habría que dar:

 

BBDD

Clasificar paquetes

De alguna manera, tendremos que almacenar la información del paquete Orquestador que se está ejecutando. Es decir, por un lado tener una tabla con la información de todos los paquetes contenidos en ese Orquestador, y por otro, registrar esa información en otra tabla en tiempo de ejecución del Orquestador donde conforme vaya finalizando la ejecución de cada paquete se actualice la información correspondiente en esa tabla.

Por tanto, el primer paso sería clasificar los paquetes por área de negocio. Para ello utilizaremos dos tablas:

  • Tabla [PersonalOrchestrators]

Con esta tabla almacenaremos la información de los Orquestadores que tengamos en nuestro proyecto SSIS.

Gestión de reintentos. Orquestador

    • OrchestratorId: campo definido como IDENTITY
    • Name: nombre del orquestador
    • Source: DWH o STG

 

  • Tabla [PersonalOrchestratorsDetail]

En esta tabla almacenaremos los paquetes contenidos en cada uno de los Orquestadores de nuestro proyecto SSIS.

Gestión de reintentos. Orquestador

    • OrchestratorId: id del Orquestador (relacionado con el campo OrchestratorId de la tabla PersonalOrchestrators).
    • line: indica el orden.
    • idProject:? id del Proyecto al que pertenece la tabla. Este campo no es necesario si no se tiene una tabla de relación de tabla-proyecto.
    • destination_table_schema:
      • Para los proyectos de staging su valor será “STG”.
      • Para los proyectos de DWH: sus posibles valores serían:
        • Si es una dimensión: dim.
        • Si es un hecho: fact.
        • Si es una tabla auxiliar: aux.
    • destination_table_name:?nombre de la tabla de destino.

 

Dotar de lógica al orquestador

Una vez tenemos clasificada dicha información, pasaremos a tener unas tablas que varíen su información en tiempo de ejecución, de tal manera que conforme finalice la ejecución de un paquete se actualice la información. Deberemos tener las siguientes tablas:

  • Tabla [ExecutionControlDetailsTemplate]

En esta tabla se insertan los paquetes contenidos en un Orquestador. Para que la lógica de los reintentos funcione, es necesario que estén informados los paquetes del Orquestador en cuestión. Nuestro paquete SSIS Orquestador leerá de esta tabla para saber qué paquetes lo forman e insertar dicha información en una tabla de control de ejecuciones (ExecutionControlDetails).

Gestión de reintentos. Orquestador

 

  • Tabla [ExecutionControlDetails]

En esta tabla se controla la ejecución de todos los paquetes que contiene el Orquestador en curso. El paquete SSIS debe informar esta tabla en el momento de su ejecución, y si todos los paquetes se ejecutan satisfactoriamente, vaciará los registros correspondientes.

    • OrchestratorName: nombre del Orquestador en curso.
    • PackageName: todos los paquetes del Orquestador.
    • Success: inicialmente todos a 0. Cambia el valor a 1 cuando finaliza correctamente.
    • ErrorNumber: número de error en caso de fallar. Sino, 0 por defecto.

Gestión de reintentos. Orquestador

 

  • Procedimiento stp_etl_table_load_info_set_start

Este procedimiento será el encargado de consultar si hay políticas activas para el Orquestador en ejecución y si dicha política ha expirado. Si está activa y todavía no ha expirado, insertará los paquetes correspondientes al Orquestador en ejecución en la tabla ExectionControlDetails.

Este procedimiento también llevará el control de la tabla ExecutionControl (se verá en detalle más adelante), donde si se ha alcanzado la fecha de expiración o agotado el número de reintentos, borrará la información de ese Orquestador e insertará una nueva con una nueva fecha de expiración y un nuevo número de reintentos. Llegado a este punto, el Orquestador se volvería a ejecutar desde el principio.

CREATE PROCEDURE [log].[stp_etl_table_load_info_set_start] @schemaName AS SYSNAME,
@tableName AS SYSNAME,
@type AS CHAR(1),
@serverExecutionId BIGINT,
@xcs XML = ”
AS
DECLARE @id INT;
INSERT INTO [log].etl_table_load_info
([schema_name],
[table_name],
[type],
[start_date],
[ssis_server_execution_id]
)
VALUES
(@schemaName,
@tableName,
@type,
SYSDATETIME(),
@serverExecutionId
);
SET @id = SCOPE_IDENTITY();
UPDATE [log].etl_table_load_info
SET
[$xcs] = @xcs
WHERE id = @id;
DECLARE @EnableExecutionControlPolicy BIT, @OrchestratorName SYSNAME, @ExpirationTimeMinutes INT, @isOrchestrator INT, @retryNumber INT;
SELECT @EnableExecutionControlPolicy = EnableExecutionControlPolicy,
@OrchestratorName = PackageName,
@ExpirationTimeMinutes = ExpirationTimeMinutes,
@isOrchestrator = isOrchestrator,
@retryNumber = RetryNumber
FROM log.etl_table_load_info
WHERE id = @id;
IF(@EnableExecutionControlPolicy = 1
AND @isOrchestrator = 1)
BEGIN/* Check if we have to do a new execution */DELETE FROM log.ExecutionControl
WHERE ExpirationDate < GETDATE()
OR retrynumber = 0;
— Deletion cascade of packagesIF NOT EXISTS
(
SELECT 1
FROM LOG.ExecutionControl
WHERE OrchestratorName = @OrchestratorName
)
BEGIN
— insert header of execution control
INSERT INTO LOG.ExecutionControl
(OrchestratorName,
ExpirationDate,
retrynumber
)
SELECT @OrchestratorName,
DATEADD(MINUTE, @ExpirationTimeMinutes, GETDATE()),
@retryNumber;
— insert details of execution control
INSERT INTO log.ExecutionControlDetails
(OrchestratorName,
PackageName,
Success,
ErrorNumber
)
SELECT OrchestratorName,
PackageName,
0,
0
FROM log.ExecutionControlDetailsTemplate
WHERE OrchestratorName = @OrchestratorName;
END;
ELSE
BEGIN
UPDATE LOG.ExecutionControl
SET retrynumber=retrynumber-1
WHERE OrchestratorName = @OrchestratorName
END
END;
RETURN @id;GO

 

  • Procedimiento stp_etl_table_load_info_set_end_cs

Este procedimiento será el encargado de actualizar la tabla ExecutionControlDetails. Actualizará el campo status a 1 si el paquete se ejecuta bien; en caso contrario, lo actualizará a 0. De esta manera cuando el Orquestador se vuelva a ejecutar, leerá sólo los paquetes con status=0 y será los que ejecute.

Una vez finalice la ejecución del Orquestador, se borrarán todos los paquetes del Orquestador de la tabla ExecutionControl.

CREATE PROCEDURE [log].[stp_etl_table_load_info_set_end_cs]
@rowId INT,
@xcs XML,
@status CHAR(1)
AS
BEGINUPDATE [log].etl_table_load_info
SET [$xcs] = @xcs, [end_date] = SYSDATETIME(), [status] = @status
WHERE id = @rowIdDECLARE @EnableExecutionControlPolicy BIT, @Packagename SYSNAME, @ExpirationTimeMinutes INT, @isOrchestrator INT, @retryNumber INT;
SELECT @EnableExecutionControlPolicy = EnableExecutionControlPolicy,
@Packagename = PackageName,
@ExpirationTimeMinutes = ExpirationTimeMinutes,
@isOrchestrator = isOrchestrator,
@retryNumber = RetryNumber
FROM log.etl_table_load_info
WHERE id = @rowId;– If the package exists in the list -no matter over wich orchestrator- we mark as done or failed
update log.ExecutionControlDetails
set Success= case when @status=’S’ THEN 1 ELSE 0 END ,
ErrorNumber=ErrorNumber+case when @status=’S’ THEN 0 ELSE 1 END
WHERE PackageName=@Packagename;– if the package is an orchestrator, and success we must drop de log.executionControl
— some packages could not be executed because can be marked as “active_for_load=’N’ “if (@status=’S’ or (select count(*) from log.ExecutionControlDetails where Success=0 and OrchestratorName=@Packagename)=0)
BEGIN
delete from log.ExecutionControl where OrchestratorName=@Packagename;
ENDEND

 

Política de reintentos

Por otro lado, estaría bien determinar el modo de actuación en los reintentos, es decir, cuántos reintentos se quieren hacer y qué fecha de expiración tienen. Por dos motivos: el primero, podemos tener tantas políticas de reintentos que queramos (habrá situaciones en las que no nos interese aplicar más de X reintentos en un Orquestador, o que se puedan realizar reintentos en un margen corto o largo de tiempo); y el segundo poder controlar si dicha política está permitida o no en algún momento. Toda esta información la podemos almacenar en una tabla donde nuestro Orquestador al ejecutarse lo primero que haga sea leer de dicha tabla y si se le aplica una política, insertar en otra tabla para ese Orquestador en cuestión la información relativa a los reintentos. Dichas tablas serían:

  • Tabla [Configuration]

Tabla de configuración de políticas de reintentos.

    • EnableExecutionControlPolicy: indica si está permitido la política de control de ejecuciones.
    • ExpiratioinTimeMinutes: minutos restantes para expirar esta política.
    • RetryNumber: número de reintentos asignados a esta política.

Gestión de reintentos. Orquestador

En la imagen se ve la definición de una política activa (EnableExecutionControlPolicy=1) con 3 reintentos (RetryNumber=3).

 

  • Tabla [ExecutionControl]

En esta tabla se inserta la fecha de expiración que tiene cada orquestador como límite para ejecutar los reintentos. Esta tabla se informa en el momento de ejecutar.

    • OrchestratorName: nombre del orquestador.
    • ExpirationDate: fecha máxima para ejecutarse el orquestador.
    • Retrynumber: número de reintentos restantes. Este valor se actualiza al actual-1 conforme vaya ejecutando reintentos.

Gestión de reintentos. Orquestador

En la imagen que los reintentos en dicho orquestador expirarán si llegamos a la fecha indicada en ExpirationDate o gastamos todos los reintentos. En este caso, sólo nos quedaría 1.

 

SSIS: paquete orquestador

A continuación se detalla los pasos que habría que dar para configurar nuestro Orquestador.

 

Configurar Execute SQL Task “Check Configuration” que recupere la información de la tabla Configuration para comprobar si este Orquestador tiene política de reintentos.

Gestión de reintentos. Orquestador

select top (1) EnableExecutionControlPolicy,ExpirationTimeMinutes,RetryNumber from md.Configuration;

 

Gestión de reintentos. Orquestador

 

Configurar Execute SQL Task “Set Load Start” que llame al procedimiento “stp_etl_table_load_info_set_start”, donde internamente se consulta si hay política activa y el estado de la misma.

Gestión de reintentos. Orquestador

DECLARE @rowId INT;
EXEC @rowId = [log].stp_etl_table_load_info_set_start ‘STG’, ‘Orchestrator_Illion_ODBC_Shipments’, ‘I’, ?,?;
SELECT CAST(@rowId AS INT) AS RowId

 

Configurar Execute SQL Task “Check Configuration [nombre_paquete]” que mire si el paquete a continuación se ejecutó correctamente, y si es así no lo ejecutará.

Gestión de reintentos. Orquestador

SELECT COUNT(*) Ejecutar FROM LOG.ExecutionControlDetails WHERE Success!=0 AND PackageName=’Load_Illion_STG1_DLH_HLP2_ILION_articleshipmentstype_ODBC’

 

Gestión de reintentos. Orquestador

 

Configurar Execute SQL Task “Set Load End Failure”. Si ha habido algún fallo, se llama al procedimiento “stp_etl_table_load_info_set_end_cs”, donde internamente se marca como Failed  (“F”).

Gestión de reintentos. Orquestador

EXEC [log].stp_etl_table_load_info_set_end_cs ?, ?, ‘F’;

 

Configurar Execute SQL Task “Set Load End Success”. Si se ejecuta bien, se llama al procedimiento “stp_etl_table_load_info_set_end_cs”, donde internamente se marca como Success (“S”).

Gestión de reintentos. Orquestador

EXEC [log].stp_etl_table_load_info_set_end_cs ?, ?, ‘S’;

 

 

 

0 Shares:
Share 0
Tweet 0
Share 0

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

— Previous article

ScaleOut SSIS

Next article —

Power BI Dataflows: Machine Learning en dos clicks!

You May Also Like