none
TSQL - FTP file to client site

    Question

  • My employer's company has a client that wants daily SSRS downloads (not sure if this SSRS will be exported as txt, pdf or XLS in the subscription) ftp'd to their site. The clients has supplied a directory, FTP address, user name and password.  I have downloaded a stored procedure from http://www.sqlservercentral.com/scripts/Miscellaneous/30638/ (below) that allows me to FTP but it appears that this goes to a server's root directory.  Can anyone look at this and comment?  Ideally I want to FTP to a specific location on the client's server. 

    The exec command is as follows:

    EXEC dbo.up_FTPPushFile 'c:\temp\test.txt', 'server', 'user', 'password'

    Thank you. 

    Brian

    create proc [dbo].[up_FTPPushFile]
    @file_to_push varchar(255),
    @ftp_to_server varchar(255),
    @ftp_login varchar(255),
    @ftp_pwd varchar(255)
    as
    Set Nocount On
    --STEP 0
    --Ensure we can find the file we want to send.
    Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
    Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push
    IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
    BEGIN
     Drop table #FileExists
     RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)
     RETURN 1
    END 
    --STEP 1
    --Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push
    --batch file will hold 4 records:
    --1) login
    --2) password
    --3) ftp command and file to push
    --4) exit command
    declare @sql varchar(255), @cmd varchar(255), @batch_ftp varchar(255), @ret int
    set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'
    set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
    set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /T /c'
    Create table ##temp_ftp_bat(ftp_batch varchar(255), file_to_push varchar(255))
    Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)
    Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)
    Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)
    Insert into ##temp_ftp_bat values ('bye', @file_to_push)
    EXEC master.dbo.xp_cmdshell @cmd
    Drop table ##temp_ftp_bat
    --STEP 2
    --Ensure we can find the batch file we just created.
    Delete #FileExists
    Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp
    IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
    BEGIN
     Drop table #FileExists
     RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)
     RETURN 1
    END 
    Drop table #FileExists
    --STEP 3
    --Execute newly created .bat file, save results of execution
    Create table #temp_ftp_results (ftp_output varchar(255))
    set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server
    Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
    IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))
    BEGIN
     Drop table #temp_ftp_results
     RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)
     RETURN 1
    END 
    Drop table #temp_ftp_results
    --STEP 3
    --delete batch file
    set @cmd = 'del '+@batch_ftp
    EXEC master.dbo.xp_cmdshell @cmd

    Friday, February 17, 2017 2:39 PM

Answers

  • You need to add a new statement after this one:

    Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)

     --add the below one

    Insert into ##temp_ftp_bat values ('cd /remote_directory_on_ftp_server')

    replacing remote_directory_on_ftp_server with the name of the remote directory on the ftp server.

     


    • Edited by Hilary Cotter Friday, February 17, 2017 2:50 PM
    • Marked as answer by BrianJohn Friday, February 17, 2017 8:56 PM
    Friday, February 17, 2017 2:50 PM

All replies

  • You need to add a new statement after this one:

    Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)

     --add the below one

    Insert into ##temp_ftp_bat values ('cd /remote_directory_on_ftp_server')

    replacing remote_directory_on_ftp_server with the name of the remote directory on the ftp server.

     


    • Edited by Hilary Cotter Friday, February 17, 2017 2:50 PM
    • Marked as answer by BrianJohn Friday, February 17, 2017 8:56 PM
    Friday, February 17, 2017 2:50 PM
  • Hilary, Thank you for the assistance.  I'll run this code and provide some more feedback.

    Brian

    Friday, February 17, 2017 3:22 PM
  • Hilary

    The stored procedure is not liking that line.  The new line has been added to existing code and resembles the following

    ...
    Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)
    Insert into ##temp_ftp_bat values ('cd /remote_directory_on_ftp_server) -- New line
    Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)

    The SSMS error is as follows below.  When double clicked the 'New line' is highlighted
    Msg 213, Level 16, State 1, Line 28
    Column name or number of supplied values does not match table definition.

    Your thoughts?

    Brian



    • Edited by BrianJohn Friday, February 17, 2017 7:07 PM
    Friday, February 17, 2017 5:25 PM
  • To get rid of this error for your new line:

    Insert into ##temp_ftp_bat values ('cd /D:\!General_DZ\members\in\IVS\01000', @file_to_push) -- New line


    Friday, February 17, 2017 6:41 PM
    Moderator
  • Found the solution!
    Updated line:
    Insert into ##temp_ftp_bat values ('cd remote_directory_on_ftp_server', @file_to_push)

    Works great.

    Friday, February 17, 2017 7:08 PM