SQL SERVER SSIS : comment récupérer la liste des fichiers d'un ftp

Problématique :

Nous voulons récupérer la liste des fichiers présents à partir d'un serveur FTP avec un Package SSIS et insérer cette liste dans une table SQL.

Généralement le besoin est de récupérer que les fichiers qui n'ont pas été traiter.

Par défaut SSIS ne propose pas cette opération.

Solution :

L'idée est de passer par un composant Tâche de script pour récupérer la liste des fichiers dans une variable, par la suite on insert le contenu de cette variable dans une table pour cela :

Voici le lien vers le Package déjà configuré :  ICI

1. Créer 4 variables :

Liste variables

• FtpFileListXML : Nous allons stocker la liste des fichiers dans cette variable
• FtpFileType : Cette variable va nous servir de récupérer le type des fichiers
• FtpServer : Le nom de serveur FTP
• FtpWorkingDirectory : Le répertoire du serveur FTP

    2.  Créer une tâche de script comme suit :

Tâche de script

• Variable en lecture seul : FtpWorkingDirectory
• Variables en lecture écriture : FtpFileListXML

     3. Modifier le script et coller le code suivant :

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Text     ' need to add this one

 _
 _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum

    Public Sub Main()

        ' STEP 1
        Dim ftpFileNameListXML As New StringBuilder
        ftpFileNameListXML.AppendLine("")

        ' STEP 2
        Dim ftpcm As ConnectionManager = Dts.Connections("FTP")
        Dim ftp As FtpClientConnection = _
                    New FtpClientConnection(ftpcm.AcquireConnection(Nothing))
        Dim ftpFileNames() As String
        Dim ftpFolderNames() As String

        ' STEP 3
        ftp.Connect()
        ftp.SetWorkingDirectory(Dts.Variables("FtpWorkingDirectory").Value.ToString())
        ftp.GetListing(ftpFolderNames, ftpFileNames)
        ftp.Close()

        ' STEP 4
        Dim i As Integer
        For i = 0 To ftpFileNames.GetUpperBound(0)
            ' add xml element
            ftpFileNameListXML.Append("")
        Next i

        ' STEP 5
        ftpFileNameListXML.AppendLine("")
        Dts.Variables("FtpFileListXML").Value = ftpFileNameListXML.ToString()

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

    4. Créer une connexion FTP

Dans la barre de gestionnaires de connexions faite un clic droite et sélectionner "Nouvelle Connexion" :

Nouvelle connexion

Sélectionner le type" FTP" :

Gestionnaire de connexion FTP

Renseigner les informations du FTP et tester la connexion.

Configurer l'expressions ServerName avec la variable "FtpServer"

Renommer cette connexion en "FTP" par défaut c'est "Gestionnaire de connexion FTP"

    5.  Créer la table de stockage de la liste des fichiers :

CREATE TABLE dbo.stg_FtpFileList
(
 FileType nvarchar(50) NOT NULL, 
 FileName nvarchar(50) NOT NULL
)

    6. Créer une procédure stockée pour Spliter le contenu XML de la variable FtpFileListXML :

CREATE PROCEDURE [dbo].[usp_PutFtpFileList]
  @fileType nvarchar(50)
, @xml  xml
AS
BEGIN
  SET NOCOUNT ON;
 
  DELETE FROM [dbo].[stg_FtpFileList]
  WHERE [FileType] = @fileType
 
  INSERT INTO [dbo].[stg_FtpFileList] (
         [FileType]
        ,[FileName]
  )
  SELECT
   @fileType
  ,doc.col.value('@name', 'nvarchar(50)') filename
  FROM @xml.nodes('//file') doc(col)
END

    7. Créer une "Tâche d'exécution de requêtes SQL"

Tâche d'exécution de requêtes SQL

• Configurer votre connexion vers le serveur SQL et la base de données là ou vous avez créer la table "stg_FtpFileList" et la procédure "usp_PutFtpFileList"
• Mapper les paramètres comme suit :

Mapper les paramètres

• Le premier paramètre avec la variable "FtpFileType"
• Le deuxième paramètre avec la varibale "FtpFileListXML"

Et votre Package devrais ressembler à ça :

Package SSIS

Enjoy