Procédure stockée permettant d'envoyer un fichier depuis le serveur de BDD vers un FTP (unix ou windows)
en construisant un fichier de commandes temporaire par BCP (fichier supprimé en fin de process)
Marche sur n'importe quelle base de données SQL-server 2005 ou +
Suffit d'exécuter le create procedure
Si le param @rep_fait existe, le fichier source est "déplacé" vers ce rep. après le "send" sinon il est supprimé
Source / Exemple :
-- =============================================
-- Author: Yannick Laussanne
-- Create date: 26/06/2012
-- Description: Envoi d'un fichier existant sur le srv de BDD vers un FTP Unix ou Windows
--
-- On construit un fichier de commandes ftp temporaire et on exécute
-- la commande console ftp -s:"fichier_de_cmd" serveurFtp
--
-- Attention : si le param @rep_fait ne commence pas par [DISQUE]:\ on le concatène à @path
-- sinon on le prend tel quel
-- =============================================
CREATE PROCEDURE [dbo].[util_SendFTP]
@serveurFTP nvarchar(300), /*serveur FTP sans préfixe*/
@loginFTP nvarchar(150), /*login ftp*/
@passFTP nvarchar(150), /*pass ftp*/
@workingDirFTP nvarchar(150), /*doit-on se positionner sur un ou plusieurs sous répertoire du ftp*/
@path varchar(255), /*Répertoire physique de travail sur le srv de BDD*/
@file varchar(255), /*Fichier dans le "@path" à envoyer sur le ftp*/
@rep_fait varchar(255), /*Répertoire de move final pour les fichiers envoyés, si vide, supprime le fichier source*/
@loginBCP varchar(50), /*login de la connexion SQL pour BCP*/
@passBCP varchar(50), /*pass de la connexion SQL pour BCP*/
@msgRet varchar(500) output, /*Eventuellemnt, retourne un msg d'erreur*/
@debug bit = 0 /*Affiche les commandes*/
AS
BEGIN
SET NOCOUNT ON;
SET @msgRet = 'OK'
DECLARE
@cmd varchar(4000),
@retShell int,
@retShellFTP int,
@req varchar(2500),
@tmpCMDFTP varchar(100), /* Fichier de cmd FTP temporaire */
@fileToSend varchar(255) /* path + file */
/* Normalement, les paramètres de chemin ont été validés par l'appelant */
IF RIGHT(@path,1) != '\'
SET @path = @path + '\'
/* Le fichier à envoyer */
SET @fileToSend = @path + @file
/* Le Fichier de cmd FTP temporaire */
SET @tmpCMDFTP = 'tmpCMDFTP_' + replace(CONVERT(varchar,GETDATE(),114),':','') + '.txt'
/* Crée et rempli le fichier de cmd FTP tmp en passant par une table de travail */
DECLARE @tableTravail nvarchar(150) ; SET @tableTravail = db_name() + N'.dbo.W_CMDFTP_TMP'
IF OBJECT_ID(@tableTravail, N'U') is null
CREATE TABLE W_CMDFTP_TMP (
_cpt int identity(1,1),
_ligne nvarchar(1024)
);
ELSE
TRUNCATE TABLE W_CMDFTP_TMP;
/* Construit les commandes selon le protocole ftp -s */
INSERT INTO W_CMDFTP_TMP VALUES(@loginFTP); /*login*/
INSERT INTO W_CMDFTP_TMP VALUES(@passFTP); /*pass*/
IF ISNULL(@workingDirFTP,'') != '' /*se place sur un sous-rep*/
INSERT INTO W_CMDFTP_TMP VALUES('cd ' + @workingDirFTP);
IF CHARINDEX('/', @loginFTP)>0 /*si login unix/EDI*/
BEGIN
INSERT INTO W_CMDFTP_TMP VALUES('prompt'); /*obligatoire pour faire marcher le mode interractif*/
INSERT INTO W_CMDFTP_TMP VALUES('prompt'); /*obligatoire pour faire marcher le mode interractif*/
END
INSERT INTO W_CMDFTP_TMP VALUES(N'send "'+@fileToSend+'"'); /*cmd FTP send OU mput si binary*/
INSERT INTO W_CMDFTP_TMP VALUES(N'bye'); /*ferme ftp*/
/* Requete d'écriture ligne à ligne */
SET @req = 'SELECT _ligne FROM '+db_name()+'..W_CMDFTP_TMP ORDER BY _cpt'
/* Création et remplissage du fichier de cmd tmp */
SET @cmd = 'BCP "' + @req + '" queryout "' + @path + @tmpCMDFTP + '" -c -CACP -U'+@loginBCP+' -P'+@passBCP+';'
IF @debug = 1
print @cmd
EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
IF @retShell != 0
BEGIN
SET @msgRet = 'Erreur bloquante sur la commande BCP : ' + @cmd
RETURN 1;
END
/* Ouvre le ftp en donnant le fichier de params créé ci-dessus */
/* Ce qui exécutera le "send" du fichier donné vers le répertoire FTP ouvert */
SET @retShellFTP = 0
DECLARE @OUT TABLE(_id int identity(1,1), _out varchar(1024))
SET @cmd = 'ftp -s:"' + @path + @tmpCMDFTP + '" ' + @serveurFTP
IF @debug = 1
print @cmd
INSERT INTO @OUT(_out)
EXEC @retShellFTP = xp_cmdshell @cmd
/* Essaye de renvoyer un message d'erreur cohérent */
IF @retShellFTP != 0
BEGIN
SET @msgRet = 'ERREUR INCONNUE: ' + @cmd
END
ELSE IF exists(SELECT 1 FROM @OUT WHERE _out like '%cannot log in%' or _out like '%Please login with%') or
not exists(SELECT 1 FROM @OUT WHERE _out is not null)
BEGIN
SET @retShellFTP = 99
SET @msgRet = 'ERREUR CONNEXION: ' + @cmd + char(13) + isnull((
SELECT top 1 replace(replace(_out,'¶','o'), 'Ú','e') FROM @OUT
WHERE isnull(_out,'') like '%cannot log in%' or isnull(_out,'') like '%Please login with%'),'?')
END
ELSE IF exists(SELECT 1 FROM @OUT WHERE _out like '%H_te inconnu%' or _out like '%Commande non valide%' or _out like '%Non connect_%')
BEGIN
SET @retShellFTP = 99
SET @msgRet = 'ERREUR SERVEUR: ' + @cmd + char(13) + isnull((
SELECT top 1 replace(replace(_out,'¶','o'), 'Ú','e') FROM @OUT
WHERE isnull(_out,'') like '%H_te inconnu%' or isnull(_out,'') like '%Commande non valide%' or isnull(_out,'') like '%Non connect_%'),'?')
END
/* Supprime le fichier de cmd FTP tmp */
SET @cmd = 'del "' + @path + @tmpCMDFTP + '"'
IF @debug = 1
print @cmd
EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
IF @retShell != 0
BEGIN
IF LEN(@msgRet)>0
SET @msgRet = @msgRet + char(13)
SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande DEL : ' + @cmd
END
/* Si demandé et aucune erreur, déplace ou supprime le fichier qui vient d'etre envoyé vers le ftp */
IF @retShellFTP = 0
BEGIN
IF len(ISNULL(@rep_fait,'')) > 0
BEGIN
IF RIGHT(@rep_fait,1) != '\'
SET @rep_fait = @rep_fait + '\'
IF @rep_fait like '[A-Z]:%\'
SET @cmd = 'MOVE /Y "' + @fileToSend + '" "' + @rep_fait + @file + '"'
ELSE
SET @cmd = 'MOVE /Y "' + @fileToSend + '" "' + @path + @rep_fait + @file + '"'
IF @debug = 1
print @cmd
EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
IF @retShell != 0
BEGIN
IF LEN(@msgRet)>0
SET @msgRet = @msgRet + char(13)
SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande MOVE : ' + @cmd
END
END
ELSE
BEGIN
SET @cmd = 'del "' + @fileToSend + '"'
IF @debug = 1
print @cmd
EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
IF @retShell != 0
BEGIN
IF LEN(@msgRet)>0
SET @msgRet = @msgRet + char(13)
SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande DEL : ' + @cmd
END
END
END
IF @debug = 1
print char(13) + @msgRet
RETURN @retShellFTP;
END
Conclusion :
Exemple d'appel :
DECLARE
@msgOut varchar(500),
@retProc int
EXEC @retProc = util_SendFTP
@serveurFTP = '10.46.XXX.XXX',
@loginFTP = 'leLogin',
@passFTP = 'lePassword',
@workingDirFTP = 'myDirectoryFTP/mySubDirectoryFTP',
@path = 'C:\TestSendFTP\',
@file = 'toto.txt',
@rep_fait = 'C:\TestSendFTP\FAIT\',
@loginBCP = 'sa',
@passBCP = 'sql',
@msgRet = @msgOut output,
@debug = 0
print '@retProc = ' + cast(@retProc as varchar)
print '@msgOut = ' + @msgOut
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.