Search code examples
sqlsql-serverstored-proceduresftpbcp

Moving files from one SQL Server to another through FTP by using a stored procedure and bulk copy program (bcp) utility

Below is the procedure I am trying to write:

  1. First I need to generate a file in the SQL Server 1 with some data that can be queried from the same server

  2. Then I need to move the file to a FTP server from SQL Server 1, from there I need to fetch the details of the file from SQL Server 2.

How can I do with the help of the bcp utility?

Solution

  • Create a file in sql server with queried data

        DECLARE @FileName varchar(1000),@bcpCommand varchar(1000),
            @FileGeneratePath varchar(100)
    DECLARE @exe_path4 VARCHAR(200) = ' cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn & ';

    Set @FileGeneratePath = '\\192.168.XX.XXX\Files\'
    Set @FileName = REPLACE('SampleFile'+ CONVERT(varchar(10),GETDATE(),100)+'.xls',' ','') --'\\192.168.29.111\Files\authors'+ Convert(varchar(40),Getdate(),121) + '.csv'

    SET @bcpCommand = @exe_path4 +' bcp.exe "SELECT top 10 * from [192.168.XX.XXX].DATABASE.dbo.FamilyAccountDetails where Convert(varchar(10),LastUpdatedon,100)  > Convert(varchar(10),Getdate()-30,100)" queryout "' 
    SET @bcpCommand = @bcpCommand + @FileGeneratePath + @FileName + '" -c -t"|" -Uusername -Ppassword -S192.168.XX.XXX'

    EXEC master..xp_cmdshell @bcpCommand, no_output

    exe_path4 is the path of bcp exe. For more details of bcp utility check this. and also this

    xp_cmdshell is an extended stored procedure and it allows you to execute an operating system command via a SQL Statement. Extended stored procedures are programs written in langauges like C++ that are then attached to an instance of SQL Server. Once attached, they can be referenced just like a regular stored procedure.

    no_output in the xp_cmdshell act like set nocount on.

    Stored procedure to move a file from sql server to an ftp

    In the below store procedures , i created a text file to execute in command prompt to move file to the ftp and vice versa. Both files generated in the SQL Server temp folder, not in the local temp. you can also give a particular folder for the file generation.

    /*     
    Exec spPullFileToFTP
    @FTPServer = '192.168.YY.YYY' ,
    @FTPUser = 'username' ,
    @FTPPWD = 'password' ,
    @SourcePath = '\\192.168.YY.YYY\Files\' ,
    @SourceFiles = 'SampleFileJan16201.xls' ,
    @DestPath = 'FileToCopy/New/' 

        */
Create Procedure spPullFileToFTP
 @FTPServer varchar(128),
 @FTPUser varchar(128),
 @FTPPwd varchar(128),
 @SourcePath varchar(128),
 @SourceFiles varchar(128),
 @DestPath varchar(128),
 @FTPMode varchar(10)=''
as

Set Nocount On

SET @FTPMode = 'binary' -- ascii, binary or blank for default.

DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)

-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'

DROP TABLE #tempvartable

-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'

-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
select @DestPath = replace(replace(replace(@DestPath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'

-- Build the FTP script file.
select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd


select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

IF LEN(@FTPMode) > 0
BEGIN
 select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
 EXEC master..xp_cmdshell @cmd

END
IF LEN(@DestPath) > 0
BEGIN
 select @cmd = 'echo ' + 'cd ' + @DestPath + ' >> ' + @tempdir + @workfile
 EXEC master..xp_cmdshell @cmd

END
select @cmd = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
print @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd


-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a

-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
go

    **Output File **

    open 192.168.YY.YYY 
Username
Password
prompt  
binary 
cd FileToCopy/AdvisoryPortal/ 
mput \\192.168.YY.YYY\Files\SampleFileJan16201.xls 
quit

    Stored Procedure to move a file from ftp to SQL Server

    /*     
    exec spPullFileFromFTP  
    @FTPServer = '192.168.YY.YYY' ,
    @FTPUser = '11111' ,
    @FTPPWD = 'FtpPassword' ,
    @SourcePath = '/FileToCopy/Sample/' ,
    @SourceFiles = 'SampleFileJan12201.xls' ,
    @DestPath = '\\192.168.XX.XXX\Files\New\' 



        */
Create procedure spPullFileFromFTP
 @FTPServer varchar(128),
 @FTPUser varchar(128),
 @FTPPwd varchar(128),
 @SourcePath varchar(128),
 @SourceFiles varchar(128),
 @DestPath varchar(128),
 @FTPMode varchar(10)=''
as
Begin

    SET @FTPMode = 'binary' -- ascii, binary or blank for default.

    DECLARE @cmd varchar(1000)
    DECLARE @workfile varchar(128)
    DECLARE @nowstr varchar(25)

    -- Get the %TEMP% environment variable.
    DECLARE @tempdir varchar(128)
    CREATE TABLE #tempvartable(info VARCHAR(1000))
    INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
    SET @tempdir = (SELECT top 1 info FROM #tempvartable)
    IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
    DROP TABLE #tempvartable

    -- Generate @workfile
    SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
    SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'

    -- Deal with special chars for echo commands.
    select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
    select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
    select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
    select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')
    IF RIGHT(@DestPath, 1) = '\' SET @DestPath = LEFT(@DestPath, LEN(@DestPath)-1)

    -- Build the FTP script file.
    select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
    select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
    select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
    select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd

    IF LEN(@FTPMode) > 0
    BEGIN
        select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
        EXEC master..xp_cmdshell @cmd
    END

    select @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd

    IF LEN(@SourcePath) > 0
    BEGIN
        select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
        EXEC master..xp_cmdshell @cmd
    END

    select @cmd = 'echo ' + 'mget '  + @SourceFiles + ' >> ' + @tempdir + @workfile
    --select @cmd = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd


    select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd


    -- Execute the FTP command via script file.
    select @cmd = 'ftp -s:' + @tempdir + @workfile
    create table #a (id int identity(1,1), s varchar(1000))
    insert #a
    EXEC master..xp_cmdshell @cmd
    select id, ouputtmp = s from #a

    -- Clean up.
    drop table #a
    select @cmd = 'del ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd

End

    **Output File **

    open 192.168.YY.YYY
Username
Password
prompt  
binary 
lcd \\192.168.XX.XXX\Files\New 
cd /FileToCopy/AdvisoryPortal/ 
mget SampleFileJan12201.xls 
quit

    If any doubts in the commands, please check this.

    To bulk copy from file to Database table

            Declare @SQL varchar(500)
        set @SQL = 'BULK INSERT  BGL_CRM_FamilyAccountDetails  FROM '+''''+'//192.168.YY.XXX/Files/New/'+ @FileName +''''+ 
        ' WITH (DATAFILETYPE =' +''''+'char' +''''+', FIELDTERMINATOR = '+''''+'|'+''''+', ROWTERMINATOR = '+''''+'\n'+''''+')'                

        EXEC(@SQL)