Skinnysite.net

... un modo di esprimersi.

Skip to content

Riempire una tabella con dati provenineti da una connessione FTP

Valutazione attuale:

/ 0

Dettagli: Categoria principale: Code Snippets; Categoria: Transact-SQL; Pubblicato 29 Aprile 2011; Visite: 455

Premetto subito che una buona parte di questo esempio è stata tratta dal sito di Nigel Rivett e il codice transact da cui sono partito si trova a questa pagina: http://www.nigelrivett.net/index.html#FTP
L'esempio sottostante mostra come sia possibile scaricare un file via FTP e inserirlo dentro una tabella del nostro database. Per la spiegazione del comando "xp_cmdshell" rimando all'articolo "Salvare il risultato di una query dentro un file di testo"

In sostanza questo script ha bisogno che le variabili per la connesisone siano valorizzate correttamente, dopodiché attraverso il comando "xp_cmdshell" si scrive un file di testo (con l'ausilio dell'operarore di ridirezione), che contiene tutte le informazioni per avviare il comando di FTP.
Successivamente dopo aver svuotato preventivamente la tabella che voglio popolare, attraverso l'istruzione BULK INSERT leggo il file e inserisco i dati dentro la tabella.
L'istruzione BULK INSERT è abbastanza semplice da utilizzare, tuttavia ha molti parametri che possono essere approfonditi alla pagina ufficiale dell'istruzione.
Dopo aver caricato il file cancello i files che sono stati creati durante l'importazione e rimetto a posto la configurazione originaria.

declare @cmd varchar(1000)
declare @workfilename varchar(128)
declare @FTPServer varchar(128)
declare @FTPUser varchar(128)
declare @FTPPWD    Varchar(128)
declare @FTPPath varchar(128)
declare @FTPFileName varchar(128)
declare @SourcePath    varchar(128)
declare @SourceFile    varchar(128)
declare @workdir varchar(128)

--Preparo i dati per la connesisone FTP
set    @FTPServer = 'server'
set    @FTPUser = 'user'
set    @FTPPWD = 'password'
set    @FTPPath = 'tmp\'
set    @FTPFileName = 'file.txt'
set    @SourcePath = 'c:\tmp\'
set    @SourceFile = 'file.txt'
set    @workdir = 'c:\tmp\'

--Aggiorno la configurazione
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

--Abilito l'uso del comando xp_cmdshell
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

--Fine aggiornamento
--File di log delle operazioni FTP
select @workfilename = 'ftp_config_file.txt'

--Pulisco eventuali caratteri pericolosi che potrebbero essere dentro i parametri
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'','^>')

select @cmd = 'echo open ' + @FTPServer + ' > ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd

select @cmd = 'echo '+ @FTPUser + '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'echo get ' + @FTPPath + @FTPFileName + ' ' + @SourcePath + @SourceFile
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'echo quit' + ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

exec master..xp_cmdshell @cmd

--Svuoto la tabella prima di ricaricarla
TRUNCATE TABLE dbo.your_table

BULK INSERT dbo.your_table
FROM 'c:\tmp\file.txt'
WITH (FIELDTERMINATOR = ';', FIRSTROW = 1)

--cancello i file che ho creato per l'importazione
select @cmd = 'del ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'del c:\tmp\file.txt'
exec master..xp_cmdshell @cmd
--Fine cancellazione

--Disabilito l'uso del comando xp_cmdshell
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE

--Aggiorno la configurazione
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
--Fine aggiornamento