Skip to content
Learni
Voir tous les tutoriels
Business Intelligence

Comment intégrer SSIS SSAS SSRS pour BI en 2026

22 minEXPERT
SQL ServerSSISSSASSSRSBusiness Intelligence
Read in English

Introduction

En 2026, les stacks BI Microsoft restent incontournables pour les entreprises gérant des volumes massifs de données on-premise ou hybrides. SSIS excelle dans l'ETL robuste, SSAS Tabular offre des modèles analytiques scalables avec DAX, et SSRS délivre des rapports pixel-perfect intégrés à Power BI. Ce tutoriel expert vous guide pour orchestrer un pipeline complet : extraction/chargement via SSIS, modélisation OLAP avec SSAS, et publication de rapports SSRS.

Pourquoi c'est crucial ? Les performances sub-secondes en requête MDX/DAX et l'automatisation via TMSL/PowerShell réduisent les TCO de 40%. Nous partons d'une base de données source pour aboutir à un rapport dynamique, avec tous les scripts copier-collables. Idéal pour architectes BI gérant des téraoctets. (132 mots)

Prérequis

  • SQL Server 2022+ (Developer/Enterprise) avec SSIS, SSAS, SSRS installés en mode natif.
  • Visual Studio 2022 + SSDT (SQL Server Data Tools) pour développement initial.
  • SQL Server Management Studio (SSMS) 20+.
  • PowerShell 7+ avec modules SqlServer et ReportingServicesTools.
  • Base AdventureWorks installée (téléchargeable sur docs.microsoft.com).
  • Droits sysadmin sur instances SSIS/SSAS/SSRS.

Étape 1: Préparation de la base de données source et cible

Commencez par créer une base source simulée avec données de ventes et une cible pour le data warehouse. Cela illustre un ETL typique : truncate, insert agrégé. Utilisez SSMS pour exécuter les scripts.

Création des bases et tables

01_create_databases.sql
USE [master];
GO

IF DB_ID('BI_Source') IS NOT NULL DROP DATABASE BI_Source;
CREATE DATABASE BI_Source;
GO

USE BI_Source;
CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    Product NVARCHAR(50),
    Category NVARCHAR(30),
    Quantity INT,
    UnitPrice DECIMAL(10,2),
    SaleDate DATE,
    Region NVARCHAR(20)
);
GO

IF DB_ID('BI_Target') IS NOT NULL DROP DATABASE BI_Target;
CREATE DATABASE BI_Target;
GO

USE BI_Target;
CREATE TABLE DimProduct (
    ProductKey INT IDENTITY PRIMARY KEY,
    Product NVARCHAR(50),
    Category NVARCHAR(30)
);

CREATE TABLE FactSales (
    SaleKey INT IDENTITY PRIMARY KEY,
    ProductKey INT,
    Quantity INT,
    TotalAmount DECIMAL(12,2),
    SaleDate DATE,
    Region NVARCHAR(20),
    LoadDate DATETIME DEFAULT GETDATE()
);
GO

Ce script efface et recrée les bases source (ventes brutes) et cible (étoilé : dim/fact). Il prépare un schéma martien pour SSIS ETL. Piège : toujours vérifier les collations pour éviter les erreurs de tri.

Insertion de données d'exemple

02_seed_data.sql
USE BI_Source;
INSERT INTO Sales (Product, Category, Quantity, UnitPrice, SaleDate, Region) VALUES
('Laptop Pro', 'Electronics', 5, 1200.00, '2024-01-15', 'Europe'),
('Mouse Wireless', 'Accessories', 20, 25.00, '2024-01-16', 'North America'),
('Keyboard Mech', 'Accessories', 10, 150.00, '2024-02-01', 'Europe'),
('Desktop Tower', 'Electronics', 3, 1800.00, '2024-02-05', 'Asia'),
('Laptop Pro', 'Electronics', 8, 1200.00, '2024-02-10', 'North America');
GO

-- Vérification
SELECT COUNT(*) FROM Sales; -- 5 rows

Insertion de 5 lignes réalistes pour tester l'ETL. Les totaux seront calculés en SSIS. Bonne pratique : utilisez des dates récentes pour valider les partitions temporelles en SSAS.

Étape 2: Création et déploiement du package SSIS

SSIS gère l'ETL : truncate dims/facts, lookup pour clés, insert agrégé. Importez le XML dans VS SSDT (Nouveau > Projet Integration Services > Importer Package). Déployez sur SSIS Catalog (SSISDB).

Package SSIS ETL complet (Data Flow)

ETL_Sales.dtsx
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" xmlns:DTSProp="www.microsoft.com/SqlServer/Dts/ReadWriteObjects" xmlns:DTSPropT="www.microsoft.com/SqlServer/Dts/ReadWriteObjects/Type" xmlns:SQLTASK="www.microsoft.com/SqlServer/Dts/SQLTask" DTS:ExecutableType="SSIS.Package.2"><DTS:LoggingOptions DTS:LoggingOptionsCustom="0" DTS:LoggingOptionsDefault="1"></DTS:LoggingOptions><DTS:Container DTS:LoggingOptions="{FDB415D0-9ECE-4B00-9B2F-8F00F6B2A9D0}" DTS:PrecedenceConstraintsVisible="1"><DTS:ConnectionManager DTS:ConnectionString="Data Source=.;Initial Catalog=BI_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" DTS:ConnectionManagerID="{A1B2C3D4-E5F6-7890-ABCD-EF1234567890}" DTS:ConnectionManagerTypeID="{BB7879FA-D5F6-497A-80CB-9AB16E3B3D4E}" DTS:DesignTimeProperties="Provider=SQLNCLI11.1;DataTypeCompatibility=80;DefaultDataSource=.;DefaultCatalog=BI_Source;DefaultSchema=dbo;"><DTS:ObjectData><DTS:ConnectionManager DTS:DTSID="{A1B2C3D4-E5F6-7890-ABCD-EF1234567890}"><DTS:Property DTS:Name="Provider">SQLNCLI11.1</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager><DTS:ConnectionManager DTS:ConnectionString="Data Source=.;Initial Catalog=BI_Target;Provider=SQLNCLI11.1;Integrated Security=SSPI;" DTS:ConnectionManagerID="{B2C3D4E5-F6G7-8901-BCDE-F23456789012}" DTS:ConnectionManagerTypeID="{BB7879FA-D5F6-497A-80CB-9AB16E3B3D4E}" DTS:DesignTimeProperties="Provider=SQLNCLI11.1;DefaultDataSource=.;DefaultCatalog=BI_Target;"><DTS:ObjectData><DTS:ConnectionManager DTS:DTSID="{B2C3D4E5-F6G7-8901-BCDE-F23456789012}"></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager><DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2" DTS:LoggingOptions="{FDB415D0-9ECE-4B00-9B2F-8F00F6B2A9D0}" DTS:PrecedenceConstraintsVisible="1" DTS:RefId="{C3D4E5F6-G7H8-9012-CDEF-345678901234}" DTS:ExecutionStatus="NotReady" DTS:IdentifyingObjectName="Data Flow Task" DTS:ObjectName="Data Flow Task"><DTS:PipelineTask DTS:CreationDate="02/01/2026 10:00:00" DTS:CreationName="\Package\Data Flow Task" DTS:LoggingOptions="{FDB415D0-9ECE-4B00-9B2F-8F00F6B2A9D0}" DTS:ObjectName="Data Flow Task" DTS:RefId="{C3D4E5F6-G7H8-9012-CDEF-345678901234}"><DTS:PipelineEngine DTS:CreationDate="02/01/2026 10:00:00" DTS:DefaultBufferMaxRows="10000" DTS:DefaultBufferSize="10485760" DTS:EngineThreads="5"><DTS:ObjectData><DTS:ComponentMetadata DTS:ComponentName="OLE DB Source" DTS:CreationDate="02/01/2026 10:00:00" DTS:CreationName="OLE DB Source 1" DTS:CreationType="1" DTS:Id="10" DTS:LocaleID="1033"><DTS:Property DTS:Name="AccessMode">0</DTS:Property><DTS:Property DTS:Name="ConnectionManagerID">{A1B2C3D4-E5F6-7890-ABCD-EF1234567890}</DTS:Property><DTS:Property DTS:Name="ConnectionManagerName">BI_Source</DTS:Property><DTS:Property DTS:Name="OpenRowset">dbo.Sales</DTS:Property><DTS:Property DTS:Name="SqlCommand">SELECT Product, Category, SUM(Quantity) AS Quantity, SUM(Quantity * UnitPrice) AS TotalAmount, SaleDate, Region FROM Sales GROUP BY Product, Category, SaleDate, Region</DTS:Property></DTS:ComponentMetadata><DTS:ComponentMetadata DTS:ComponentName="OLE DB Destination" DTS:CreationDate="02/01/2026 10:00:00" DTS:CreationName="OLE DB Destination 1" DTS:CreationType="1" DTS:Id="20" DTS:LocaleID="1033"><DTS:Property DTS:Name="ConnectionManagerID">{B2C3D4E5-F6G7-8901-BCDE-F23456789012}</DTS:Property><DTS:Property DTS:Name="ConnectionManagerName">BI_Target</DTS:Property><DTS:Property DTS:Name="OpenRowset">dbo.FactSales</DTS:Property><DTS:Property DTS:Name="BulkInsertCheckConstraints">false</DTS:Property><DTS:Property DTS:Name="RowsPerBatch">0</DTS:Property><DTS:Property DTS:Name="MaximumInsertCommitSize">0</DTS:Property></DTS:ComponentMetadata></DTS:ObjectData></DTS:PipelineEngine></DTS:PipelineTask></DTS:Executable></DTS:Container><DTS:PackageVariable DTS:Name="User::LoadDate" DTS:DataType="7" DTS:ValueType="Reference"><DTS:VariableValue xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:DataSubType="DT_DBTIMESTAMP" /></DTS:PackageVariable></DTS:Executable>

Package minimal avec Data Flow : source SQL agrégé depuis BI_Source.Sales vers BI_Target.FactSales (assume DimProduct populated séparément). Importez en VS SSDT pour valider/editer. Piège : adaptez ConnectionManagerID GUIDs uniques lors de l'import.

Déploiement SSIS sur catalog SSISDB

03_deploy_ssis.sql
USE SSISDB;
GO

-- Créer folder si absent
IF NOT EXISTS (SELECT * FROM SSISDB.catalog.folders WHERE name = 'BI')
EXEC catalog.create_folder @folder_name = N'BI', @folder_description = N'BI Pipeline';

-- Déployer package (après build .ispac ou via VS)
-- Exemple T-SQL pour exec post-déploiement
EXEC [SSISDB].[catalog].[create_execution] 
    @folder_name=N'BI', 
    @project_name=N'ETL_Project', 
    @package_name=N'ETL_Sales.dtsx',
    @use32bitruntime=FALSE,
    @reference_id=NULL;

DECLARE @execution_id BIGINT;
SELECT @execution_id = MAX(execution_id) FROM catalog.executions;
EXEC [SSISDB].[catalog].[start_execution] @execution_id;

-- Vérif
SELECT * FROM catalog.executions WHERE execution_id = @execution_id;

Ce T-SQL crée un dossier catalog, assume package déployé via VS (projet .ispac), lance l'exécution. Surveillez avec logs SSISDB. Piège : activez CLR strict pour sécurité en prod.

Étape 3: Modélisation et déploiement SSAS Tabular

SSAS Tabular pour OLAP rapide. Utilisez TMSL (JSON) scriptable pour CI/CD. Connectez à BI_Target, créez table FactSales avec mesure Total Sales.

Script TMSL pour modèle SSAS Tabular

04_ssas_model.tmsl
{
  "create": {
    "parentObject": {
      "object": {
        "database": "BI_Model"
      }
    },
    "object": {
      "name": "BI_Model",
      "database": "BI_Model",
      "compatibilityLevel": 1500,
      "model": {
        "tables": [
          {
            "name": "FactSales",
            "columns": [
              {"name": "ProductKey", "dataType": "int64", "sourceColumn": "ProductKey"},
              {"name": "Quantity", "dataType": "int64", "sourceColumn": "Quantity"},
              {"name": "TotalAmount", "dataType": "double", "sourceColumn": "TotalAmount"},
              {"name": "Region", "dataType": "string", "sourceColumn": "Region"},
              {"name": "SaleDate", "dataType": "dateTime", "sourceColumn": "SaleDate"}
            ],
            "partitions": [
              {
                "name": "LivePartition",
                "dataSource": "SqlServer localhost BI_Target",
                "query": "SELECT ProductKey, Quantity, TotalAmount, Region, SaleDate FROM FactSales"
              }
            ],
            "measures": [
              {
                "name": "Total Sales",
                "expression": "SUM([TotalAmount])",
                "formatString": "#,##0.00",
                "dataType": "double"
              }
            ]
          }
        ]
      }
    }
  }
}

TMSL crée DB Tabular ''BI_Model'' avec table FactSales partitionnée live sur BI_Target, mesure DAX Total Sales. Exécutez via SSMS (Script > TMSL) ou PowerShell. Piège : spécifiez exact dataSource name depuis SSAS.

Déploiement SSAS via PowerShell

05_deploy_ssas.ps1
Import-Module SqlServer

$server = "localhost\TABULAR"
$tmslPath = "C:\path\to\04_ssas_model.tmsl"
$tmslScript = Get-Content $tmslPath -Raw

Invoke-ASCmd -Server $server -Database "BI_Model" -Script $tmslScript

# Refresh partition
$refresh = @{
    "type" = "full";
    "objects" = @(
        @{
            "database" = "BI_Model";
            "table" = "FactSales";
            "partition" = "LivePartition"
        }
    )
} | ConvertTo-Json -Depth 10

Invoke-ASCmd -Server $server -Database "BI_Model" -Script $refresh

Write-Output "Modèle SSAS déployé et rafraîchi."

PowerShell utilise module SqlServer pour exécuter TMSL et refresh partition. Adaptez server/port. Piège : activez ''Allow XMLA endpoint'' pour Azure AS, mais natif ok.

Étape 4: Création et publication du rapport SSRS

SSRS pour rapports paginés. Créez un RDL connecté à SSAS, avec table matricielle sur Total Sales par Region. Publiez via PowerShell pour automatisation.

Définition RDL rapport SSRS complet

06_SalesReport.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Tablix Name="Tablix1">
        <TablixBody>
          <TablixColumns>
            <TablixColumn><Width>2in</Width></TablixColumn>
            <TablixColumn><Width>2in</Width></TablixColumn>
            <TablixColumn><Width>2in</Width></TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="RegionHeader"><Value>Region</Value></Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="TotalSalesHeader"><Value>Total Sales</Value></Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Region"><Value>=Fields!Region.Value</Value><CanGrow>true</CanGrow></Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="TotalSales"><Value>=Fields!Total_Sales.Value</Value></Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <Group Name="Details"/>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <TablixColumnHierarchy>
          <TablixMembers />
        </TablixColumnHierarchy>
        <DataSetName>SalesDS</DataSetName>
      </Tablix>
    </ReportItems>
  </Body>
  <DataSources>
    <DataSource Name="SSAS_DS">
      <ConnectionProperties>
        <DataProvider>msmdpump.dll</DataProvider>
        <ConnectString>Data Source=localhost\TABULAR; Initial Catalog=BI_Model</ConnectString>
      </ConnectionProperties>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="SalesDS">
      <Query>
        <DataSourceName>SSAS_DS</DataSourceName>
        <CommandText>SELECT NON EMPTY { [Measures].[Total Sales] } ON COLUMNS, NON EMPTY { ([Region].[Region].[All]) } ON ROWS FROM [Model]</CommandText>
        <QueryLanguage>mdx</QueryLanguage>
      </Query>
      <Fields>
        <Field Name="Region"><DataField>Region</DataField></Field>
        <Field Name="Total_Sales"><DataField>Total Sales</DataField></Field>
      </Fields>
    </DataSet>
  </DataSets>
  <Width>6.5in</Width>
  <Page>
    <Width>8.5in</Width>
    <Height>11in</Height>
  </Page>
</Report>

RDL simple avec Tablix sur MDX query SSAS : Total Sales par Region. Ouvrez en Report Builder/VS pour preview. Piège : MDX syntaxe stricte ; testez en SSMS SSAS.

Publication SSRS via PowerShell

07_publish_ssrs.ps1
Install-Module -Name ReportingServicesTools -Force

$server = 'http://localhost/ReportServer'
$rdlPath = 'C:\path\to\06_SalesReport.rdl'

Write-RsCatalogItem -ReportServerUri $server -Path '/BI/SalesReport' -InputFilePath $rdlPath -DataSourceName 'SSAS_DS' -Overwrite

# Créer datasource si absent
$dsPath = '/BI/SSAS_DS'
$dsContent = @{
    ItemType = 'DataSource'
    Name = 'SSAS_DS'
    Path = $dsPath
    TypeName = 'Microsoft.AnalysisServices.AdomdClientConnection'
    ExtensionSettings = @{ Connection = @{ ConnectString = 'Data Source=localhost\TABULAR; Initial Catalog=BI_Model' } }
} | ConvertTo-Json

New-RsCatalogItem -ReportServerUri $server -Path $dsPath -InputFileContent $dsContent -CatalogItemType 'DataSource'

Write-Output 'Rapport SSRS publié : http://localhost/Reports/browse/BI/SalesReport'

PowerShell publie RDL et crée datasource SSAS. Accédez via http://localhost/Reports. Piège : utilisez ReportServer (admin) vs Reports (portal) ; activez RS Windows Auth.

Bonnes pratiques

  • Automatisez avec CI/CD : Intégrez TMSL/PowerShell dans Azure DevOps/GitHub Actions pour déploiements zero-downtime.
  • Sécurisez les connexions : Utilisez Managed Service Accounts ou Key Vault pour credentials SSIS/SSAS.
  • Optimisez performances : Partitions automatiques en SSAS, CDC en SSIS pour incrémental ETL, cache SSRS.
  • Monitoring : Activez Extended Events SSISDB, DMVs SSAS, traces SSRS.
  • Scalabilité : Migrez vers Azure Analysis Services pour cloud bursting.

Erreurs courantes à éviter

  • GUIDs SSIS non uniques : Recréez ConnectionManagerIDs à chaque import XML.
  • Partitions SSAS non rafraîchies : Toujours Invoke refresh post-ETL.
  • MDX/RDL syntaxe : Validez queries en SSMS avant embed.
  • Permissions SSISDB : db_exec pour exec, pas sysadmin en prod.

Pour aller plus loin