EXTRACT - ZIP - FTP

Objective: How to extract data in csv and then FTP:

First extract the data from stored procedure or sql query using bcp, osql or sqlcmd

Then Zip it

Then finally FTP

Procedures are as follows:

SET QUOTED_IDENTIFIER OFF
go
Create Procedure P_EXTRACT_ZIP_FTP
as
Begin
DECLARE @return_value INT
Declare @FilePath Varchar(MAX)
DECLARE @SQL VARCHAR(8000)
Declare @pname varchar(75)
DECLARE @filename varchar(75)
DECLARE CURSOR_NAME CURSOR for select [pname],[filename] from TABLENAME(NOLOCK)
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @pname, @filename
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @Query VARCHAR(255)
SET @Query=@pname
DECLARE @dbname VARCHAR(255)
SET @dbname='dbname'
DECLARE @Headers int
Set @Headers=999999
SET @FilePath = 'D:\Reports\foldername\send\'+rtrim(ltrim(right(convert(varchar(11),getdate()-16, 120),11)))+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.csv'
/** This code is just for examples
--SET @SQL = 'bcp "' +cast(@pname as varchar(max))+'" queryout "'+ cast(@FilePath as varchar(MAX))+'" -c -r\n -t\, -T -w -V 90 -eerr'+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.txt >> errors-'+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.txt'
--SET @SQL Sqlcmd -S Servername -E -d northwind -Q "Select * from employees" -o C:\output_file.csv -s ,
--SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s"," -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s"," -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
**/
SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s, -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
print @SQL
EXECUTE @return_value = master..xp_cmdshell @SQL
SET QUOTED_IDENTIFIER off
DECLARE @cmd AS VARCHAR(128)
set @cmd = "master..xp_cmdshell 'D:\Reports\foldername\GZIP D:\Reports\foldername\send\*.csv'"
EXEC(@cmd)
EXEC MONITOR.dbo.[sp_FtpPutFile_ReportServer]
@FTPServer = 'server IP'
,@FTPUser = 'username'
,@FTPPWD = 'password'
,@FTPPath = '/'
,@SourcePath = 'D:\Reports\foldername\send\'
,@DestPath = 'D:\Reports\foldername\moved\'
,@SourceFile = '*.CSV.GZ'
,@workdir = 'D:\Reports\foldername\'

FETCH NEXT FROM CURSOR_NAME INTO @pname, @filename

END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
End

TABLE STRUCTURE:

create table TABLENAME
(id int identity(1,1),pname varchar(75), filename varchar(75))
go
insert into TABLENAME (pname, filename) values ('EXEC DBNAME.DBO.PROCEDURENAME','_FILENAME')

FTP MOVING PROCEDURE:

CREATE PROCEDURE [dbo].[sp_FtpPutFile_ReportServer]
(
@FTPServer varchar(128)
,@FTPUser nvarchar(128)
,@FTPPWD nvarchar(128)
,@FTPPath nvarchar(128)
,@SourcePath nvarchar(128)
,@SourceFile nvarchar(128)
,@workdir nvarchar(128)
,@DestPath nvarchar(128)
,@binaryFile bit = NULL
)
AS
BEGIN
SET NOCOUNT ON;

IF(@binaryFile IS NULL)
begin
SET @binaryFile = 1
end

DECLARE @cmd varchar(1000) -- command to execute
DECLARE @workfilename varchar(20) -- file to store ftp command batch

SET @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
SET @FTPServer = REPLACE(REPLACE(REPLACE(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
SET @FTPUser = REPLACE(REPLACE(REPLACE(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPWD = REPLACE(REPLACE(REPLACE(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPath = REPLACE(REPLACE(REPLACE(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

-- insert FTP servername to @workfilename
SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP username to @workfilename
SET @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP password to @workfilename
SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

IF @binaryFile = 1
begin
-- set the file transfer type to binary
SET @cmd = 'echo bin' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
end

-- change the working directory on the remote computer
SET @cmd = 'echo cd ' + @FTPPath + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- copy all files of a type @SourceFile from @SourcePath into @FTPPath
SET @cmd = 'echo mput ' + @SourcePath + @SourceFile + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- end the FTP session with the remote computer and exit ftp
SET @cmd = 'echo quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- execute the @workfilename (i - turn off interactive prompting for the mput command)
SET @cmd = 'ftp -i -s:' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd--, no_output

-- move all files of a type @SourceFile from @SourcePath into @DestPath (Y - suppress prompting to overwrite)
SET @cmd = 'move /Y ' + @SourcePath + @SourceFile + ' ' + @DestPath
EXEC master..xp_cmdshell @cmd, no_output

END