Sql-server proc. send_ftp

Commenter

Contenu du snippet

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

A voir également

S'inscrire maintenant

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.

Du même auteur (yann_lo_san)