Metadata-driven pipelines : la continuité du Master Package SSIS

Organiser des flux, c’est une question qui m’a accompagné tout au long de ma carrière.

Quand j’ai commencé avec les ETL, j’ai appris “à l’ancienne” : toutes les règles de gestion codées directement dans les packages. Une approche rigide, lourde à maintenir, et qui surtout créait une dépendance forte de mes clients envers moi.

Assez vite, j’ai développé une autre méthode, que j’ai fini par appliquer dès que je le pouvais :

• utiliser l’ETL comme orchestrateur,
• confier le cœur du traitement au SQL,
• et donner à mes clients plus d’autonomie, tout en simplifiant la maintenance.

Dans ce blog, je vais partager cette approche et comparer deux contextes :

• La structure de base que j’utilise pour SSIS depuis de longues années
• Ce même modèle ré-adapté à MS Fabric et aux nouvelles options que Pipeline offre

Le Master Package SSIS : ma première brique de factorisation

Quand j’ai commencé à travaillé avec SSIS, j’ai vite cherché à éviter de répéter les mêmes flux. Dupliquer dix fois un package juste parce qu’on change le nom d’une table, pour moi, ça n’a jamais eu de sens.

La solution que j’ai mise en place, c’était le Master Package, ce n’est pas une idée nouvelle, mais je l’ai adapté à ma façon de travailler. L’idée était simple :

• construire des packages enfants très unitaires (un fichier, une table, une logique claire),
• centraliser l’exécution dans un Master qui lit une table de pilotage,
• et exécuter les sous-packages selon un scénario défini en base.

Avantages obtenus :

• factorisation maximale,
• possibilité pour mes clients d’activer/désactiver un flux par simple mise à jour en table,
• orchestration gérée sans redéploiement du projet,
• centralisation des logs de process en un seul point.

Un mécanisme de logging centralisé dans le Master Package me permettait de :

• tracer l’exécution de chaque sous-package,
• uniformiser les retours d’erreurs,
• simplifier la maintenance.

Plus besoin d’ouvrir 15 packages pour comprendre où ça avait cassé : tout remontait dans la même table de suivi.

Exemple de modèle utilisé :

CREATE TABLE dbo.Packages
(
    PackageID    INT            IDENTITY(1,1) PRIMARY KEY,
    PackageName  VARCHAR( 100 ) NOT NULL, -- Nom du .dtsx
    Description  VARCHAR( 400 ) NULL,
    IsEnabled    BIT            NOT NULL DEFAULT (1),
    CreatedAt    DATETIME2(0)   NOT NULL DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT UQ_Packages__PackageName UNIQUE (PackageName)
);
GO

/* Paramètres par défaut au niveau du package (surchargés par scénario si besoin) */
CREATE TABLE dbo.PackageParameters
(
    PackageID    INT           NOT NULL FOREIGN KEY REFERENCES dbo.Packages(PackageID),
    ParamName    VARCHAR(128)  NOT NULL,
    DefaultValue VARCHAR(4000) NOT NULL,
    CONSTRAINT PK_PackageParameters PRIMARY KEY (PackageID, ParamName)
);
GO


/* =========================================================================
   SCÉNARIOS (contextes d’exécution) & ORCHESTRATION
   ========================================================================= */
CREATE TABLE dbo.Scenarios
(
    ScenarioID   INT          IDENTITY(1,1) PRIMARY KEY,
    ScenarioName VARCHAR(100) NOT NULL, -- DAILY / BACKFILL / CLIENT_X ...
    Description  VARCHAR(400) NULL,
    IsActive     BIT          NOT NULL DEFAULT (1),
    CreatedAt    DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT UQ_Scenarios__ScenarioName UNIQUE (ScenarioName)
);
GO

CREATE TABLE dbo.ScenarioSteps
(
    ScenarioStepID  INT IDENTITY(1,1) PRIMARY KEY,
    ScenarioID      INT NOT NULL FOREIGN KEY REFERENCES dbo.Scenarios(ScenarioID),
    PackageID       INT NOT NULL FOREIGN KEY REFERENCES dbo.Packages(PackageID),
    ExecutionOrder  INT NOT NULL,
    IsActive        BIT NOT NULL DEFAULT (1),
    RetryCount      INT NOT NULL DEFAULT (0),
    ContinueOnError BIT NOT NULL DEFAULT (0),
    TimeoutSec      INT NULL,                -- optionnel : timeouts par step
    CONSTRAINT UQ_Steps__Scenario_Order UNIQUE (ScenarioID, ExecutionOrder)
);
GO

/* Surcharges de paramètres au niveau scénario→package */
CREATE TABLE dbo.ScenarioParameters
(
    ScenarioID INT            NOT NULL FOREIGN KEY REFERENCES dbo.Scenarios(ScenarioID),
    PackageID  INT            NOT NULL FOREIGN KEY REFERENCES dbo.Packages(PackageID),
    ParamName  VARCHAR(128)   NOT NULL,
    ParamValue VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_ScenarioParameters PRIMARY KEY (ScenarioID, PackageID, ParamName)
);
GO

/* Vue des paramètres “effectifs” (Scenario override > défaut package) */
CREATE VIEW dbo.vw_EffectiveParameters
AS
SELECT s.ScenarioID,
       p.PackageID,
       pp.ParamName,
       COALESCE(sp.ParamValue, pp.DefaultValue) AS EffectiveValue
  FROM dbo.Scenarios          AS s
       INNER JOIN dbo.ScenarioSteps      AS ss ON ss.ScenarioID = s.ScenarioID AND ss.IsActive = 1
       INNER JOIN dbo.Packages           AS p  ON p.PackageID   = ss.PackageID AND p.IsEnabled = 1
        LEFT JOIN dbo.PackageParameters  AS pp ON pp.PackageID  = p.PackageID
        LEFT JOIN dbo.ScenarioParameters AS sp ON sp.ScenarioID = s.ScenarioID
                                              AND sp.PackageID  = p.PackageID
                                              AND sp.ParamName  = pp.ParamName;
GO


/* =========================================================================
   LOGS CENTRALISÉS (header run + détails par step)
   ========================================================================= */
CREATE TABLE dbo.ProcessRun
(
    RunID         BIGINT       IDENTITY(1,1) PRIMARY KEY,
    ScenarioName  VARCHAR(100) NOT NULL,
    Status        VARCHAR(20)  NOT NULL, -- Started / Succeeded / Failed / PartiallyFailed
    StartTime     DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME()),
    EndTime       DATETIME2(0) NULL,
    DurationSec   INT          NULL,
    TriggeredBy   VARCHAR(128) NULL, -- Agent, utilisateur, API, etc.
    CorrelationID UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID())
);
GO

CREATE TABLE dbo.ProcessRunStep
(
    RunStepID      BIGINT        IDENTITY(1,1) PRIMARY KEY,
    RunID          BIGINT        NOT NULL FOREIGN KEY REFERENCES dbo.ProcessRun(RunID),
    ScenarioStepID INT           NULL,          -- facultatif : rattacher au modèle
    ScenarioName   VARCHAR(100)  NOT NULL,
    PackageName    SYSNAME       NOT NULL,
    Status         VARCHAR(20)   NOT NULL,      -- Started / Succeeded / Failed / Skipped / Retried
    StartTime      DATETIME2(0)  NOT NULL DEFAULT (SYSUTCDATETIME()),
    EndTime        DATETIME2(0)  NULL,
    DurationSec    INT           NULL,
    Retries        INT           NOT NULL DEFAULT (0),
    ErrorMessage   VARCHAR(2000) NULL
);
GO

/* Index utiles */
CREATE INDEX IX_ProcessRun__StartTime   ON dbo.ProcessRun     (StartTime DESC);
CREATE INDEX IX_ProcessRunStep__RunID   ON dbo.ProcessRunStep (RunID);
CREATE INDEX IX_Steps__Scenario_Order   ON dbo.ScenarioSteps  (ScenarioID, ExecutionOrder);
GO

Requêtes “maître” utilisées par le Master Package

/* 1) Plan d’exécution pour un scénario donné */
DECLARE @ScenarioName VARCHAR(100) = @ScenarioNameInput;

SELECT s.ScenarioName,
       p.PackageName,
       ss.ExecutionOrder,
       ss.RetryCount,
       ss.ContinueOnError,
       ss.TimeoutSec
  FROM dbo.ScenarioSteps AS ss
       INNER JOIN dbo.Scenarios AS s ON s.ScenarioID = ss.ScenarioID
	                                AND s.IsActive  = 1
       INNER JOIN dbo.Packages  AS p ON p.PackageID = ss.PackageID
	                                AND p.IsEnabled = 1
 WHERE s.ScenarioName = @ScenarioName
   AND ss.IsActive    = 1
 ORDER BY
       ss.ExecutionOrder ASC;

/* 2) Paramètres effectifs pour (Scénario, Package) */
DECLARE @ScenarioName VARCHAR(100) = @ScenarioNameInput;
DECLARE @PackageName  SYSNAME       = @PackageNameInput;

SELECT ep.ParamName,
       ep.EffectiveValue
  FROM dbo.vw_EffectiveParameters AS ep
       INNER JOIN dbo.Scenarios AS s  ON s.ScenarioID = ep.ScenarioID
       INNER JOIN dbo.Packages  AS p  ON p.PackageID  = ep.PackageID
 WHERE s.ScenarioName = @ScenarioName
   AND p.PackageName  = @PackageName;

Explication du modèle SSIS

Ce modèle repose sur un principe simple : séparer la logique technique de l’orchestration.

• Packages : liste des packages disponibles (chargement d’une table, traitement d’un fichier…).
  • 1 package = 1 traitement.
  • Exemple : j’importe une table, puis j’exécute la procédure associée.
• PackageParameters : paramètres par défaut au niveau package (source, cible, dates…).
  • Exemple : dans un traitement différentiel, je stock la dernière date de traitement et la réinjecte automatiquement lors de l’exécution.
• Scenarios : contextes d’exécution (DAILY, BACKFILL, CLIENT_X…).
  • Exemple : certains clients gèrent plusieurs bases identiques. Le scénario permet de passer dynamiquement d’un client à l’autre (connexion, serveur, etc.).
• ScenarioSteps : plan d’exécution qui relie un scénario à une suite de packages (ordre, retries, tolérance aux erreurs).
  • Gestion dynamique des étapes, réduction de l’effort de maintenance.
  • Au final : un simple job SQL Agent lié à cette table, simplifiant l’exploitation.
• ScenarioParameters : surcharges de paramètres pour un scénario donné.
  • Exemple : un client peut demander un filtrage particulier (plage de dates, règles locales).
• ProcessRun : entête de chaque exécution (statut global, durée, déclencheur).
  • Exemple : branchement Power BI pour suivi en temps réel des traitements.
• ProcessRunStep : détail de chaque étape reliée au RunID (statut, durée, erreurs).
  • Exemple : visualisation simple des erreurs dans Power BI, envoi d’emails en cas de problème, rapport quotidien automatisé.

Intérêt du modèle SSIS :

1. Pilotage dynamique : tout changement se fait en table, pas dans les packages.
2. Logs centralisés : un seul point de vérité pour le suivi complet.
3. Reporting intégré : mise en place rapide de dashboards ou rapports automatisés.

Le metadata-driven dans Fabric : la continuité naturelle

Avec Microsoft Fabric, je retrouve la même philosophie que celle de SSIS : séparer la logique technique de l’orchestration.

La différence : Fabric va beaucoup plus loin en termes de :

• souplesse (paramétrage),
• scalabilité (Spark, clusters dynamiques),
• observabilité (logs centralisés exploitables).

Modèle de tables Fabric — complet & factorisé :

/* =========================================================================
   UNITÉS DE TRAVAIL (WorkUnits) = l’équivalent des packages SSIS
   ========================================================================= */
CREATE TABLE dbo.WorkUnits
(
    WorkUnitID   INT          IDENTITY(1,1) PRIMARY KEY,
    WorkUnitName VARCHAR(128) NOT NULL,   -- ex : Load_Customers, Load_Sales
    WorkType     VARCHAR(50)  NOT NULL,   -- COPY / SPARK_JOB / STORED_PROC
    Target       VARCHAR(128) NULL,       -- Lakehouse, DWH, fichier…
    IsEnabled    BIT          NOT NULL DEFAULT (1),
    CreatedAt    DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT UQ_WorkUnits__Name UNIQUE (WorkUnitName)
);
GO

/* Paramètres par défaut au niveau WorkUnit */
CREATE TABLE dbo.WorkUnitParameters
(
    WorkUnitID   INT           NOT NULL FOREIGN KEY REFERENCES dbo.WorkUnits(WorkUnitID),
    ParamName    VARCHAR(128)  NOT NULL,
    DefaultValue VARCHAR(4000) NOT NULL,
    CONSTRAINT PK_WorkUnitParameters PRIMARY KEY (WorkUnitID, ParamName)
);
GO


/* =========================================================================
   SCÉNARIOS (contextes d’exécution) & ORCHESTRATION
   ========================================================================= */
CREATE TABLE dbo.FabricScenarios
(
    ScenarioID       INT          IDENTITY(1,1) PRIMARY KEY,
    ScenarioName     VARCHAR(100) NOT NULL,
    Description      VARCHAR(400) NULL,
    IsActive         BIT          NOT NULL DEFAULT (1),
    ConcurrencyLevel INT          NULL,                  -- tuning possible par scénario
    CreatedAt        DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT UQ_FabricScenarios__Name UNIQUE (ScenarioName)
);
GO

CREATE TABLE dbo.FabricScenarioSteps
(
    ScenarioStepID  INT          IDENTITY(1,1) PRIMARY KEY,
    ScenarioID      INT          NOT NULL FOREIGN KEY REFERENCES dbo.FabricScenarios(ScenarioID),
    WorkUnitID      INT          NOT NULL FOREIGN KEY REFERENCES dbo.WorkUnits(WorkUnitID),
    ExecutionOrder  INT          NOT NULL,
    IsActive        BIT          NOT NULL DEFAULT (1),
    RetryCount      INT          NOT NULL DEFAULT (0),
    ContinueOnError BIT          NOT NULL DEFAULT (0),
    ClusterSize     VARCHAR(20)  NULL,                  -- Small / Medium / Large
    SkipHeaderRows  INT          NULL,                  -- pratique pour les CSV
    Comments        VARCHAR(400) NULL,
    CONSTRAINT UQ_FabricSteps__Scenario_Order UNIQUE (ScenarioID, ExecutionOrder)
);
GO

/* Surcharges de paramètres par scénario → WorkUnit */
CREATE TABLE dbo.FabricScenarioParameters
(
    ScenarioID INT           NOT NULL FOREIGN KEY REFERENCES dbo.FabricScenarios(ScenarioID),
    WorkUnitID INT           NOT NULL FOREIGN KEY REFERENCES dbo.WorkUnits(WorkUnitID),
    ParamName  VARCHAR(128)  NOT NULL,
    ParamValue VARCHAR(4000) NOT NULL,
    CONSTRAINT PK_FabricScenarioParameters PRIMARY KEY (ScenarioID, WorkUnitID, ParamName)
);
GO

/* Vue des paramètres effectifs */
CREATE VIEW dbo.vw_FabricEffectiveParameters
AS
SELECT s.ScenarioID,
       w.WorkUnitID,
       wp.ParamName,
       COALESCE(sp.ParamValue, wp.DefaultValue) AS EffectiveValue
  FROM dbo.FabricScenarios          AS s
       INNER JOIN dbo.FabricScenarioSteps      AS ss ON ss.ScenarioID = s.ScenarioID
	                                                AND ss.IsActive = 1
       INNER JOIN dbo.WorkUnits                AS  w ON w.WorkUnitID = ss.WorkUnitID
	                                                AND w.IsEnabled = 1
        LEFT JOIN dbo.WorkUnitParameters       AS wp ON wp.WorkUnitID = w.WorkUnitID
        LEFT JOIN dbo.FabricScenarioParameters AS sp ON sp.ScenarioID = s.ScenarioID
                                                    AND sp.WorkUnitID = w.WorkUnitID
                                                    AND sp.ParamName  = wp.ParamName;
GO


/* =========================================================================
   LOGS CENTRALISÉS (entête + détails steps)
   ========================================================================= */
CREATE TABLE dbo.FabricRun
(
    RunID        BIGINT       IDENTITY(1,1) PRIMARY KEY,
    ScenarioName VARCHAR(100) NOT NULL,
    Status       VARCHAR(20)  NOT NULL, -- Started / Succeeded / Failed / Partial
    StartTime    DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME()),
    EndTime      DATETIME2(0) NULL,
    DurationSec  INT          NULL,
    TriggeredBy  VARCHAR(128) NULL, -- utilisateur, service, API
    ProcessID    UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID())
);
GO

CREATE TABLE dbo.FabricRunStep
(
    RunStepID      BIGINT        IDENTITY(1,1) PRIMARY KEY,
    RunID          BIGINT        NOT NULL FOREIGN KEY REFERENCES dbo.FabricRun(RunID),
    ScenarioStepID INT           NULL, -- rattachement au modèle
    ScenarioName   VARCHAR(100)  NOT NULL,
    WorkUnitName   VARCHAR(128)  NOT NULL,
    Status         VARCHAR(20)   NOT NULL, -- Started / Succeeded / Failed / Skipped
    StartTime      DATETIME2(0)  NOT NULL DEFAULT (SYSUTCDATETIME()),
    EndTime        DATETIME2(0)  NULL,
    DurationSec    INT           NULL,
    Retries        INT           NOT NULL DEFAULT (0),
    ErrorMessage   VARCHAR(2000) NULL
);
GO

CREATE INDEX IX_FabricRun__StartTime    ON dbo.FabricRun     (StartTime DESC);
CREATE INDEX IX_FabricRunStep__RunID    ON dbo.FabricRunStep (RunID);
CREATE INDEX IX_FabricSteps__Scenario_Order ON dbo.FabricScenarioSteps (ScenarioID, ExecutionOrder);
GO

Sélection des steps pour un scénario

SELECT s.ScenarioName,
       w.WorkUnitName,
       w.WorkType,
       w.Target,
       fs.ExecutionOrder,
       fs.SkipHeaderRows,
       fs.ClusterSize
  FROM dbo.FabricScenarioSteps AS fs
       INNER JOIN dbo.FabricScenarios AS s ON s.ScenarioID = fs.ScenarioID
	                                      AND s.IsActive = 1
       INNER JOIN dbo.WorkUnits       AS w ON w.WorkUnitID = fs.WorkUnitID
	                                      AND w.IsEnabled = 1
WHERE s.ScenarioName = @ScenarioName
  AND fs.IsActive     = 1
ORDER BY
      fs.ExecutionOrder ASC;

Explication du modèle Fabric

• WorkUnits : équivalent des packages SSIS. Une unité = 1 tâche (COPY, Spark, procédure).
• WorkUnitParameters : paramètres par défaut (source, cible, filtres).
• FabricScenarios : contextes d’exécution (DAILY, BACKFILL, CLIENT_X, TEST…), avec parallélisme possible.
• FabricScenarioSteps : orchestration des étapes (ordre, retries, tolérance aux erreurs, taille de cluster, options).
• FabricScenarioParameters : surcharges de paramètres (filtres spécifiques à un client).
• FabricRun : entête de chaque exécution (statut global, durée, déclencheur, ProcessID).
• FabricRunStep : détail de chaque étape (statut, durée, erreurs, reliée au RunID).

👉 Intérêt (similaire à SSIS, mais en mieux) :

• pilotage dynamique (tout en table, zéro code dur),
• logs centralisés avec ProcessID unique,
• scalabilité (taille de cluster, parallélisme modulables),
• observabilité (liaison native avec Power BI).

Comparaison SSIS vs Fabric

Conclusion

Que ce soit avec SSIS ou avec Fabric, mon approche reste la même : séparer la logique technique de l’orchestration et piloter les traitements par des tables.

• Avec SSIS, le Master Package m’a permis de factoriser mes flux, centraliser mes logs et donner de l’autonomie à mes clients.
• Avec Fabric, je pousse cette logique plus loin : WorkUnits remplacent les packages, les scénarios pilotent toujours, mais les logs deviennent encore plus exploitables, notamment via Power BI.

Ce que je défends, ce n’est pas un outil en particulier, mais une philosophie :

• écrire une fois,
• piloter par métadonnées,
• donner aux équipes visibilité et autonomie.

C’est cette approche qui, selon moi, fait la différence entre un projet qui devient vite un cauchemar de maintenance, et un projet qui s’adapte naturellement aux évolutions.