Dcube.fr
Retour aux articles
Data

Batch PowerShell d’envoi de fichiers Excel actualisés depuis PBIRS

Mai 11, 2023

Celine VICENTE
Retour

Cet article fait suite à celui sur « Ouvrir des classeurs Excel dans PBIRS On-Premise grâce à OOS » . Une fois que nous pouvons stocker sur Power BI Report Server (PBIRS) des classeurs Excel qui s’ouvrent directement dans notre portail web PBIRS, nous cherchons à mettre en place un batch qui récupère et actualise des classeurs Excel, contenant des Tableaux Croisés Dynamiques (TDC) connectés aux cubes Analysis Services (AS), depuis un dossier dédié sur PBIRS, et de les envoyer par mail chaque matin.

Pour cela, nous allons voir :

Architecture

Dans l’article précédent « Ouvrir des classeurs Excel dans PBIRS On-Premise grâce à OOS » , nous avions un environnement « On premise » configuré comme suit :

  • un premier serveur S1 sur lequel est installé des instances SQL Server et SQL Server Analysis Services (SSAS)
  • un second serveur S2 dédié à l’instance PBIRS (sur-ensemble de SQL Server Reporting Services (SSRS))
  • et un dernier serveur S3 intégrant Office Online Server (OOS)

Le but est de mettre en place un batch, stocké sur le serveur S1, car il sera appelé par un job SQL, qui récupèrera et redéposera des fichiers sur le serveur S2 et qui communiquera avec une passerelle de messagerie sortante (SMTP) traitant les e-mails avant qu’ils ne soient distribués aux destinataires.

Prérequis

Installation d’Excel sur le serveur Power BI

Pour permettre au batch PowerShell d’actualiser des rapports Excel il faut installer un client Excel sur le serveur hébergeant PBIRS.

Ouvrons Excel avec le profil du compte qui exécutera le script PowerShell. Dans les options Excel, nous devons configurer un répertoire approuvé pour le téléchargement des fichiers Excel et leurs rafraichissements. Pour cela, dans les options, choisissons « Centre de Gestion de la confidentialité » et cliquons sur « Paramètres du Centre de gestion de la confidentialité… ».

Une autre fenêtre s’ouvre. Dans « Emplacements approuvés », nous retrouvons la liste de tous les dossiers étant classifiés comme des sources sécurisées. Ajoutons maintenant le répertoire qui nous intéresse comme emplacement autorisé en cliquant sur « Ajouter un nouvel emplacement ». Pour l’exemple, nous ajoutons le dossier « C:Demo_OOS ».

Préparation des données en amont pour la date

Le but étant d’avoir un classeur Excel qui filtrent les données selon un contexte temporel : données de la veille, du jour, du mois dernier ou encore de l’année en cours. Il est évident que nous ne pouvons pas filtrer sur une date « en dur », c’est à dire par exemple : Date = « 17/04/2023 » représentant la date du jour. Car demain, cette valeur sera erronée. Dans ce cas là, il faudrait chaque jour, être capable de situer la cellule où se trouve cette date, de comprendre ce que la ou les dates sélectionnées représentes et de modifier ces dates en conséquence. Ce qui devient extrêmement complexe. Alors que nous pouvons simplement renseigner une date relative en filtre, qui se calcule en fonction du jour actuel, par exemple : Date = « J-1 » ou encore Date = « M » (mois en cours). C’est donc la mise en place d’un tel axe dans notre dimension de temps, que nous allons ajouter dans notre modèle.

Dans notre cas, nous nous basons sur des cubes AS multi dimensions mais la technique suivante est applicable de la même manière pour un cube tabulaire. Pour ajouter un calendrier relatif à notre dimension de date, nous modifions en amont la vue SQL « dbo.vwDimDate » sur laquelle se base notre dimension. Cette vue se source à son tour sur la table « dbo.DimDate » , telle que nous la trouvons dans la base de données « Adventure Works DW » .

CREATE VIEW [dbo].[vwDimDate] AS
SELECT
	*
	--jour
	,DATEDIFF(DAY,GETDATE(),FullDateAlternateKey) AS RelativeDay
	,CASE
		WHEN DATEDIFF(DAY,GETDATE(),FulDateAlternateKey) < 0 THEN ‘J’ + CAST(DATEDIFF(DAY,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
		WHEN DATEDIFF(DAY,GETDATE(),FulDateAlternateKey) = 0 THEN ‘J’
		ELSE ‘J+’ + CAST(DATEDIFF(DAY,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
	 END as RelativeDayLib
	--mois
	,DATEDIFF(MONTH,GETDATE(),FullDateAlternateKey) AS RelativeMonth
	,CASE
		WHEN DATEDIFF(MONTH,GETDATE(),FulDateAlternateKey) < 0 THEN ‘M’ + CAST(DATEDIFF(MONTH,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
		WHEN DATEDIFF(MONTH,GETDATE(),FulDateAlternateKey) = 0 THEN ‘M’
		ELSE ‘M+’ + CAST(DATEDIFF(MONTH,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
	 END as RelativeMonthLib
	--année
	,DATEDIFF(YEAR,GETDATE(),FullDateAlternateKey) AS RelativeYear
	,CASE
		WHEN DATEDIFF(YEAR,GETDATE(),FulDateAlternateKey) < 0 THEN ‘A’ + CAST(DATEDIFF(YEAR,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
		WHEN DATEDIFF(YEAR,GETDATE(),FulDateAlternateKey) = 0 THEN ‘A’
		ELSE ‘A+’ + CAST(DATEDIFF(YEAR,GETDATE(),FullDateAlternateKey) AS VARCHAR(8))
	 END as RelativeYearLib
FROM
	dbo.DimDate

Actualisons maintenant la dimension Date de notre cube et ajoutons la hiérarchie « Année relative » > « Mois relatif » > « Jour relatif ».

Cela nous permet de retrouver plus simplement nos dates, bien rangées par ordre chronologique, comme tel :

Voici un aperçu de ce que cela donne avec une utilisation sur Excel. Nous pouvons dorénavant choisir facilement une date relative, comme la journée de la veille, le mois en cours, les 5 derniers jours ou autre dans nos filtres, sans avoir chaque jour ou chaque mois à changer manuellement ce filtre. Nous avons seulement à actualiser le fichier pour rafraichir les données.

Script PowerShell d’envoi de fichiers Excel actualisés

Pour rappel, nous souhaitons faire un script PowerShell qui chaque jour, après actualisation réussie des cubes, viendrait :

  1. pour un dossier dédié de PBIRS :
    1. télécharger les classeurs Excel de ce dossier
    2. pour chaque fichier Excel trouvé
      1. lancer l’actualisation de toutes les connexions 
      2. si actualisation OK : sauvegarder et copier le fichier Excel avec horodatage
      3. charger la version actualisée dans PBIRS
  2. envoyer un mail à une liste de destinataires avec l’ensemble des classeurs Excel horodatés en pièces jointes

Pour cela, nous allons détailler chacune des fonctions utilisées, les unes après les autres, et enfin expliquer l’appel principal exécutant toutes nos actions.

Commençons d’abord par présenter les variables que ce script prend en paramètres :

  • l’URL du serveur de rapport PBRIS
  • le chemin du dossier où se situe les fichiers Excel sur le serveur de rapport
  • une liste de source de données valides (si nous détectons une connexion autre que celles dans cette liste dans un classeur Excel, nous ignorons ce fichier)
  • une liste d’adresse mail destinataires des mails avec les classeurs Excel en pièces jointes
  • une adresse mail émettrice des mails avec les classeurs Excel en pièces jointes
  • une adresse mail émettrice des mails d’erreur (si nous en rencontrons lors de l’exécution du script)
param
(
    [Parameter(Mandatory = $true)][string]$PbirsServerUrl,
    [Parameter(Mandatory = $true)][string]$ExcelWorkBooksPath,
    [Parameter(Mandatory = $true)][string[]]$Datasources,
    [Parameter(Mandatory = $true)][string]$Recipients,
    [Parameter(Mandatory = $true)][string]$FromMail,
    [Parameter(Mandatory = $true)][string]$ErrorFromMail
)

Dans la majorité des fonctions qui suivent, nous allons leur passer en paramètre l’URL de l’API du serveur de rapports PBIRS. Cette adresse est définie comme suit, en récupérant l’URL du portail et en lui spécifiant l’API.

$PbirsServerApiUrl = "$PbirsServerUrl/api/v2.0"

La fonction suivante permet de récupérer, à partir d’un emplacement et d’un portail PBIRS spécifiés, la liste de tous les fichiers Excel présents. Nous utilisons ici, l’API REST pour Power BI Report Server, et plus particulièrement la méthode GET.

Function Get-ExcelWorkBooks {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][string]$PbirsServerApiUrl,
        [Parameter(Mandatory = $true)][string]$ExcelWorkBooksPath
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }     
	
    $ExcelWorkBooksUri = "$PbirsServerApiUrl/ExcelWorkbooks"  	
    $contentType = 'application/json'
                                                      
    $filter = "`$Filter=contains(Path,`'$ExcelWorkBooksPath`')"
    $select = '$select=Name,Id'
    $uri = -join ($ExcelWorkBooksUri, '?', $filter, '&', $select)
	
    Write-Verbose "Calling $uri..."
    $ExcelFileList = Invoke-RestMethod -Uri $uri -ContentType $contentType -Method GET -UseDefaultCredentials -UseBasicParsing
	
    Write-Verbose "$($ExcelFileList.Value.Count) Excel WorkBook(s) gathered"
    return $ExcelFileList.Value
}

La fonction ci-dessous permet quand à elle de déployer un fichier Excel sur le portail PBIRS. Pour cela, nous utilisons la méthode PATCH qui permet la mise à jour du fichier. Nous lui passons en paramètres l’url le l’API PBIRS, le chemin où déposer le fichier dans Power BI et le fichier Excel.

Function Update-ExcelWorkBook {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][string]$PbirsServerApiUrl,
        [Parameter(Mandatory = $true)][PSCustomObject]$ExcelWorkBook,
        [Parameter(Mandatory = $true)][string]$File
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" } 

    $ExcelWorkBooksUri = "$PbirsServerApiUrl/ExcelWorkbooks"
    $contentType = 'application/json'
			
    $Uri = "$ExcelWorkBooksUri($($ExcelWorkBook.Id))"
    $bytes = [System.IO.File]::ReadAllBytes($File)                                      
    $body = @{                                                                          
        "@odata.type" = "#Model.ExcelWorkbook";
        "Content"     = [System.Convert]::ToBase64String($bytes);
        "ContentType" = "";
        "Description" = "";
    } | ConvertTo-Json
			
    $result = Invoke-WebRequest -Uri $Uri -Body $body -ContentType $ContentType -Method PATCH -UseDefaultCredentials -UseBasicParsing
			
    if ($result.StatusCode -eq "204") {
        Write-Verbose "Succesfully Updated $($ExcelWorkBook.Name)"
    }
    else {
        Write-Error "An Error as occured while updating pbirs workbook : $result"
        Throw "An Error as occured while updating pbirs workbook : $result"
    }
			
}

Nous allons voir maintenant comment télécharger localement un fichier Excel depuis le portail PBIRS. Pour cela, nous utilisons l’applet -OutFile qui permet d’envoyer la sortie de la commande dans un fichier. Nous spécifions pour cette fonction : l’url du portail PBIRS, le dossier où se situe le fichier sur le portail et le chemin complet du fichier Excel en local (chemin + nom du fichier).

Function Out-ExcelWorkBook {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][string]$PbirsServerApiUrl,
        [Parameter(Mandatory = $true)][PSCustomObject]$ExcelWorkBook,
        [Parameter(Mandatory = $true)][string]$File
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }
	
    $ExcelWorkBooksUri = "$PbirsServerApiUrl/ExcelWorkbooks" 
    $Uri = -join ($ExcelWorkBooksUri, '(', $ExcelWorkBook.Id, ')', '/Content/$value')                                               
	
    Write-Verbose "Calling $uri..."
    Invoke-WebRequest -UseDefaultCredentials -Uri $Uri -OutFile $File -UseBasicParsing   
	
    Write-Verbose "Wrote Excel WorkBook '$($ExcelWorkBook.name)' into $File"
}

La fonction ci-dessous, permet de découper une chaine de connexion telles que nous les trouvons dans Excel lorsque nous nous connectons à une source de données (exemple avec une source de données AS: « Provider=MSOLAP.7; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=AdventureWorksDW2014Multidimensional-EE; Data Source=monServeurAS.fr; MDX Compatibility=1; Safety Options=2; MDX Missing Member Mode=Error; Update Isolation Level=2 » ). Cela nous permettra par la suite de pouvoir récupérer uniquement un élément de cette chaine de connexion, comme par exemple la « Data Source » .

Function Convert-ConnectionToHashTable {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][PSCustomObject]$Connection
    )

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }

    $connectionHash = @{}
    $Connection.OLEDBConnection.Connection.Split(";") | ForEach-Object {
        $res = $_.Split("=")
        $connectionHash.Add($res[0], $res[1])
    }
    return $connectionHash
}

La fonction suivante permet de créer un objet de type Excel. 

Function Create-ExcelObject {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][PSCustomObject]$ExcelWorkBook,
        [Parameter(Mandatory = $true)][string]$LocalDownloadPath
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }
	
    # ouverture du classeur Excel
    $ExcelObj = New-Object -ComObject "Excel.Application"      # instanciation d'un objet Excel
    $ExcelObj.visible = $False                                 # on ne le rend pas visible sur le serveur (en tâche de fond)
    $ExcelObj.DisplayAlerts = $False; # affichage des alertes et messages d'exécution
    return $ExcelObj                
}

Afin de rester cohérent dans notre démarche, nous avons choisi de ne pas nous porter garant des données que nous ne maitrisons pas. Ainsi, nous avons défini une « white list » de serveurs que nous supervisons. Dès lors que nous trouvons un fichier Excel qui a une connexion qui n’appartient pas à cette liste, nous ignorons ce fichier.

Notre « white list » est instanciée en début du script via le paramètre du script listant toutes les connexions autorisées :

$DataSourceWhiteList = $Datasources.Split(',')

D’où la fonction ci-dessous, qui contrôle que toutes les connexions d’un fichier passé en paramètre, appartiennent à notre liste de « Data Source » autorisées. 

Function Test-Connection {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][PSCustomObject]$WorkBook
    )

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }

    $ValidDataSource = $True                  
		
    foreach ($Connection in $Workbook.Connections) {
        $datasource = (Convert-ConnectionToHashTable $connection)["data source"]
	 
        If ($DataSourceWhiteList -eq $datasource) {
            Write-Verbose "$datasource found in whitelist -- OK"
        }
        Else {
            Write-Warning "$datasource not found in whitelist -- KO"
            $ValidDataSource = $False
            break
        }    
    }
	 
    return $ValidDataSource 
}

La fonction qui suit nous permet d’actualiser toutes les connexions du fichier Excel, passé en paramètre.

Function Refresh-Workbook {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][PSCustomObject]$WorkBook
    )

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }

    $script = {
        param($wb)
        $wb.RefreshAll()
        while ($wb.Connections | ForEach-Object { if ($_.OLEDBConnection.Refreshing) { $true } }) {
            Write-Verbose "Waiting for refresh..."
        }                                                                                   
    }

    $p = [PowerShell]::Create()
    $null = $p.AddScript($script).AddArgument($Workbook)
    $job = $p.BeginInvoke()
    $done = $job.AsyncWaitHandle.WaitOne()
    $p.EndInvoke($job)
}

Afin de pouvoir garder une trace des fichiers Excel actualisés, nous pouvons également avoir besoin de cette fonction qui permet de sauvegarder en local des copies du fichier Excel en ajoutant un horodatage en suffixe du nom de fichier.

Function Copy-TimeStampedWorkBook {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][string]$File,
        [Parameter(Mandatory = $true)][string]$DestinationFolder
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }

    $FileName = $file | Split-Path -Leaf
    write-Verbose "FileName: $FileName"
    $timestamp = Get-Date -Format yyyymmdd
    write-Verbose "Timestamp: $timestamp"
    $TimestampFile = $FileName.replace('.xlsx', "_$timestamp.xlsx")
    write-Verbose "TimestampFile: $TimestampFile"
    $Destination = "$DestinationFolder$TimestampFile"
    Write-Verbose "Destination: $Destination"
    Copy-Item -Path $File -Destination $Destination
    If (Test-Path $Destination) {
        Write-Verbose "$File copied to $Destination"
    }
    else {
        Write-Error "An error Occured while copied timestamped Workbook"
        Throw "An error Occured while copied timestamped Workbook"
    }

}

La fonction suivante permet d’arrêter tous les processus Excel en cours sur le serveur.

Function Stop-ExcelProcess {
    $excelProcess = Get-Process -Name "Excel" -ErrorAction SilentlyContinue
    if ($null -ne $excelProcess) {
        Stop-Process -Name "EXCEL"
    }
}

Ci-dessous, la fonction présentée permet de fermer l’objet Excel passé en paramètre et de quitter l’application Excel. 

Function Close-Quit {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][PSCustomObject]$WorkBook,
        [Parameter(Mandatory = $true)][Object]$ExcelObj
    )         

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }
	
    if ($null -ne $WorkBook) {
        $WorkBook.Close()
    }
    if ($null -ne $ExcelObj) {
        $ExcelObj.Quit()
    }
	
    Stop-ExcelProcess
}

Maintenant, nous abordons l’envoi de mail. Pour cela, nous avons besoin en paramètre d’une adresse mail émettrice du message, d’une liste de destinataires, d’un texte utilisé comme « objet » du mail, d’un texte utilisé comme « corps » du mail et des pièces jointes, dans notre cas nos fichiers Excel actualisés.

Function Send-Mail {
    param (
        [string]$From,
        [string]$Recipients,
        [string]$Subject,
        [string]$Body,
        [Object[]]$Attachment
    )

    $PSBoundParameters.Keys | ForEach-Object { Write-Verbose "$_ = '$($PSBoundParameters[$_])'" }
	          
    $hostname = hostname
    $Subject = "[$hostname]$Subject"
    $smtpServer = "mon-serveur-smtp"
    Write-Verbose "smtpServer: $smtpServer"
    Write-Verbose "from: $from"
    Write-Verbose "Attachment:"
    $Attachment | foreach-object { Write-Verbose "$_" }
	
    Write-Verbose "Sending Mail..."
    if ($null -ne $Attachment) {
        send-mailmessage -from $From -to $Recipients.Split(';') -subject $Subject -Body $Body -Attachments $Attachment -smtpServer $smtpServer -Encoding UTF8
    }
    else {
        send-mailmessage -from $From -to $Recipients.Split(';') -subject $Subject -Body $Body -smtpServer $smtpServer -Encoding UTF8
    }
    Write-Verbose "...Done"
}

N’oublions pas une dernière petite fonction pour la route qui nous permet de garder une trace de l’exécution du script dans un fichier de log. Il sera d’autant plus utile lors d’un plantage, car nous enverrons par mail ce fichier pour nous aider à localiser le problème.

Function Write-Log {
    [cmdletbinding()]
    param (
        [Parameter(Mandatory = $true)][Object]$Message
    )
    $timestamp = Get-Date -Format yyyy-MM-dd-hh-mm-ss
    $log = "[$timestamp]	$Message"
    Write-output "$log"
    $log | Out-file "C:TEMPlog.txt" -Append
}

Nous abordons enfin le script principal, qui fait appel à toutes les fonctions vues précédemment. Voici les différentes étapes que nous déroulons, dans l’ordre :

  1. appel à la fonction « Get-ExcelWorkBooks » qui récupère la liste de tous les fichiers Excel présents dans un emplacement spécifique d’un portail PBIRS
  2. pour chaque fichier de la liste récupérée :
    1. appel à la fonction « Out-ExcelWorkBook » qui télécharge localement le fichier Excel depuis le portail PBIRS
    2. appel à la fonction « Create-ExcelObject » qui instancie l’objet de type Excel correspondant, afin de pouvoir le manipuler dans PowerShell puis on l’ouvre en utilisant la méthode « Workbooks.Open()« .
    3. dans ce fichier, nous vérifions que les connexions sont autorisées (dans notre « white list« )
      • si la connexion est valide :
        1. appel à la fonction « Refresh-Workbook » pour actualiser toutes les connexions du classeur Excel
        2. on sauvegarde en local le fichier actualisé grâce à la méthode « ActiveWorkbook.SaveAs()« 
        3. appel à la fonction « Close-Quit » pour fermer le fichier et quitter l’application Excel
        4. appel à la fonction « Copy-TimeStampedWorkBook » qui permet de sauvegarder une copie horodaté du fichier en local
        5. appel à la fonction « Update-ExcelWorkBook » qui déploie le fichier actualisé sur le portail PBIRS
      • si la connexion n’est pas valide, alors on ignore le fichier
        1. message d’avertissement
        2. appel à la fonction « Close-Quit » pour fermer le fichier et quitter l’application Excel
    4. on libère les objets COM précédemment créés (l’objet Excel instancié « $ExcelObj » et le fichier Excel ouvert « $WorkBook« )
    5. on supprime le fichier en local (celui non horodaté) via l’instruction « Remove-Item« 
  3. on récupère les pièces jointes (tous les fichiers Excel horodatés en local)
    • si on a au moins une pièce jointe
      1. appel à la fonction « Send-Mail » pour envoyer le mail, après avoir spécifié les informations du mail (objet et corps). Pour rappel, les informations concernant l’expéditeur et les destinataires des mails nous sont passés en paramètre du script global.
    • si aucune pièce jointe de trouvée
      1. message d’avertissement
  4. gestion des erreurs
    1. appel à la fonction « Send-Mail » pour envoyer un message d’erreur à l’équipe avec le fichier de log en pièce jointe
    2. appel à la fonction « Close-Quit » pour fermer le fichier en cours et quitter l’application Excel
  5. finalement, on libère toutes les ressources (que le script ait terminé avec ou sans erreur) en appelant la fonction « Stop-ExcelProcess« 

Voici comment cela se traduit en code :

try {
    Write-Log "Begin"
    $ExcelWorkBookList = Get-ExcelWorkBooks -PbirsServerApiUrl $PbirsServerApiUrl -ExcelWorkBooksPath $ExcelWorkBooksPath
    Write-Log "files to be processed:"	
    $ExcelWorkBookList | ForEach-Object { Write-Log "=> $($_.Name)" }

    $ExcelWorkBookList | ForEach-Object {
	 
        $File = "$LocalDownloadPath$($_.name)"

        Write-Log "Exporting '$($_.Name)' file to '$LocalDownloadPath'..." 
        Out-ExcelWorkBook -PbirsServerApiUrl $PbirsServerApiUrl -ExcelWorkBook $_ -File $File -LocalDownloadPath $LocalDownloadPath
        Write-Log "...Done"

        Write-Log "Opening '$($_.Name)' for update..."
        $ExcelObj = Create-ExcelObject -ExcelWorkBook $_ -LocalDownloadPath $LocalDownloadPath
        Write-Log "ExcelObj created..."
        $WorkBook = $ExcelObj.Workbooks.Open($File, [System.Type]::Missing, $false)
        Write-Log "...Done"
		
        Write-Log "Validating Datasource Connection..."
        If (Test-Connection -WorkBook $WorkBook) {
			
            Write-Log "DataSource(s) valid(s) => Starting Refresh..."
            Refresh-Workbook -WorkBook $WorkBook
            Write-Log "...Done"
			
            Write-Log "Saving '$($_.Name)'..."
            $ExcelObj.ActiveWorkbook.SaveAs($File);
            Write-Log "...Done"
			
            Write-Log "Closing '$($_.Name)', Quitting Excel and stopping process..."
            Close-Quit -WorkBook $WorkBook -ExcelObj $ExcelObj
            Write-Log "...Done"			
			
            Write-Log "making a timestamped copy of '$($_.Name)'..."
            Copy-TimeStampedWorkBook -File $File -DestinationFolder $LocalDownloadPath
            Write-Log "...Done"
			
            Write-Log "Updating WorkBook in PBIRS..."
            Update-ExcelWorkBook -PbirsServerApiUrl $PbirsServerApiUrl -ExcelWorkBook $_ -File $File
            Write-Log "...Done"
			
        }
        Else {
            Write-Warning "Au moins une connexion n'est pas valide dans le classeur Excel"
            Close-Quit -WorkBook $WorkBook -ExcelObj $ExcelObj
        }
		
        $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook)
        $null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        Remove-Item $File | Out-null
    }

    $Attachment = (Get-ChildItem -Path $LocalDownloadPath -Filter *.xlsx).FullName

    If ($Null -ne $Attachment) {
        Write-Log "Sending mail to $recipients..."
        $MailDate = Get-Date -Format "dd-MM-yyyy"
        $Subject = "Classeur(s) Excel du dossier '$ExcelWorkBooksPath' du $MailDate"
        $Body = "Bonjour, Veuillez trouver ci-joint le(s) classeur(s) EXCEL actualisé(s)."
        Send-Mail -From $FromMail -Recipients $Recipients -Subject $Subject -Body $Body -Attachment $Attachment
        Write-Log "...Done"
    }
    Else {
        Write-Warning "No file has been found, please see logs for details"
    }
}
catch {
    $Subject = "[Error] Refresh Excel From PBIRS"
    $Body = "An Error as occured: $_"
    $Recipients = "mon-equipe-alerte@dcube.fr"
    Write-Log "$_"
    Write-Log "Sending error mail alert to $recipients..."
    Send-Mail -From $ErrorFromMail -Recipients $Recipients -Subject $Subject -Body $Body -Attachment "$localDownloadPath/log.txt"
    Write-Log "Closing '$($_.Name)', Quitting Excel and stopping process..."
    Close-Quit -WorkBook $WorkBook -ExcelObj $ExcelObj
    Write-Log "Throw error as the script will fail"
    Throw "$_"
}
finally {
    Write-Log "Stopping Excel Process..."
    Stop-ExcelProcess
    Write-Log "...Done"
    Write-Log "Cleaning $localDownloadPath and exit..."
	(Get-ChildItem $localDownloadPath) | ForEach-Object {
        Write-output "Removing $_"
        $_ | Remove-Item | Out-null
    }
}

Job SQL pour automatiser l’appel de ce script

Rappelons notre objectif qui est d’envoyer chaque matin un mail avec des fichiers Excel actualisés en pièces jointes à des équipes spécifiques.

Pour cela, nous avons choisi de mettre en place un job SQL qui sera appelé chaque matin. Nous avons fait le choix de ne pas mettre une planification dans le job en lui même car il nous semblait plus pertinent de l’appeler à la suite du job actualisant nos cubes, qui se lance également à la suite de nos traitements de données qui eux ont un schedule.

Notre job possède autant d’étapes (2 dans notre exemple) que nécessaire qui chacune appelle notre script sur un dossier précis de notre portail PBIRS pour des équipes dédiées. Nous pouvons ainsi organiser chaque dossier pour cibler une population spécifique, avec à l’intérieur des fichiers Excel différents.

Chaque étape est de type « CmdExec » pour permettre d’utiliser un programme exécutable, dans notre cas « powershell.exe« . Ainsi nous allons lui spécifier comme instruction le script à exécuter, sauvegardé en local sur le serveur SQL, avec un jeu de paramètres tel que nous l’avons définis dans notre script.

Vous trouvez ci-dessous un exemple de script SQL pour un tel job.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @JobName NVARCHAR(MAX) = 'update_sendmail_Excel'
DECLARE @reference_id INT
DECLARE @commandRef NVARCHAR(MAX)

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job 
	@job_name=@jobName, 
	@enabled=0, 
	@notify_level_eventlog=0, 
	@notify_level_email=0, 
	@notify_level_netsend=0, 
	@notify_level_page=0, 
	@delete_level=0, 
	@description=N'No description available.', 
	@category_name=N'technique', 
	@owner_login_name=N'sa', 
	@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id=@jobId, 
	@step_name=N'folder_01', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_success_step_id=0, 
	@on_fail_action=3, 
	@on_fail_step_id=0, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, 
	@subsystem=N'CmdExec', 
	@command=N'powershell.exe 
		-file "D:BATCHRefreshExcelFromPBIRSFolderAndSendMail.ps1" 
			"https://mon-portail-PBIRS/Reports" 
			"/folder_01" 
			"monserveurAS_01;monserveurAS_01.dcube.fr;monserveurAS_02;monserveurAS_02.dcube.fr" 
			"DG@dcube.fr;teamBI@dcube.fr" 
			"teamBI@dcube.fr" 
			"error@dcube.fr"
		', 
	@flags=32, 
	@proxy_name=N'proxy_dcube'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id=@jobId, 
	@step_name=N'folder_02', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_success_step_id=0, 
	@on_fail_action=2, 
	@on_fail_step_id=0, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, 
	@subsystem=N'CmdExec', 
	@command=N'powershell.exe 
		-file "D:BATCHRefreshExcelFromPBIRSFolderAndSendMail.ps1" 
			"https://mon-portail-PBIRS/Reports" 
			"/folder_02" 
			"monserveurAS_01;monserveurAS_01.dcube.fr;monserveurAS_02;monserveurAS_02.dcube.fr" 
			"teamRH@dcube.fr;teamAudit@dcube.fr;teamBI@dcube.fr" 
			"teamBI@dcube.fr" 
			"error@dcube.fr"
		', 
	@flags=32, 
	@proxy_name=N'proxy_dcube'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/* ---------- Etape de démarrage ----------*/
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

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Merci de nous avoir lu !

Découvrez en plus sur notre offre Modernisation Data en vous rendant sur notre page dédiée.

Modernisation Data

0 commentaires

Soumettre un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Découvrez nos autres articles

Aller au contenu principal