Post reply

DTS-DOS-FTP Connection Question

  • February 24, 2004 at 12:54 pm

    #84293

    I am using a DOS Batch File (within DTS) which calls FTP to logon to an FTP Site and downloads files. The Batch File works find but I want to be able to check the FTP Connection to make sure it was established. Is there an FTP Command that can be used to check to see if the actual connection was established? For example, is there something I can check after the ftp -d -s:C\Sign.ftp http://www.fakeftp.com statment?

    Thanks in advance,

    Kevin

  • February 24, 2004 at 3:53 pm

    #495436

    What about using the DTS FTP task with on success/failure, especially since you're already using DTS?

    Alternatively, if you want to check if the file is there, you could use something like this in an ActiveX task:

    Set objFSO  = CreateObject("Scripting.FileSystemObject")

    If objFSO.FileExists("c:\Downloads\filename") Then ...

     

  • February 25, 2004 at 8:34 am

    #495581

    I do not think that the DOS ftp connection will let you do this.

    The FTP tool in DTS is pretty weak.  If you are going to go through the trouble of adding ActiveX code I would use a third party tool for FTP tool.  Xceed Software makes a pretty good one.  Dart, http://www.dart.com is the one we use. 

    They are not that expensive, and they offer a lot more functionally then you get with both DOS and the DTS FTP. 

  • February 25, 2004 at 9:00 am

    #495595

    OR

    if you need one Free: get This one


    * Noel

  • February 25, 2004 at 9:12 am

    #495600

    I agree the FTP tool in DTS is very weak.

    I have overcome this by wrapping the ftp functionality exposed in wininet.dll(a dll deployed with windows) in a COM object using VB6, and calling this functionality from activex.

    It works a treat. Gives you better lower control and better facility to check for errors.

    For a sample app as a starting point download

    http://download.microsoft.com/download/ie4095/vbsmpftp/1/w9xnt4/en-us/VBSMPFTP.exe

    or

    http://download.microsoft.com/download/VB60Pro/VBFTP/2/Win98/En-US/VBFTP.exe

    can't remember which one I foudn useful (prob both).

     

    Jeet


    Thanks Jeet

  • February 25, 2004 at 10:20 am

    #495619

    Just a note of caution about using wininet.dll.  Make sure that you do not attempt to use it or a wrappered version of it as a server side component callable from an ASP page.  There are known issues with that and Microsoft warns against it.

  • February 25, 2004 at 11:36 am

    #495634

    Kevinsql7 - I have the same problem but since I needed to do PUT statements, the DTS ftp wasn't even an option.  I haven't settled on a solution yet but I'm looking into two leads you might find useful...

    1) This is a bit of a long work around but it seems to be very effective and the author explains it quite well.  An added bonus is that it doesn't require any third party components: http://www.sqlteam.com/item.asp?ItemID=12408

    2) I noticed that when the package runs as a scheduled job, the entire ftp session can be viewed in the Errors/Messages pane of the job step's detailed history.  Logic says that info must then be stored somewhere accessible within SQL Server.  Take a look at MSDB..sysjobhistory.message.  (You won't be able to see the entire text with a select statement.  Set the result to a nvarchar(1024) variable and print it.)

    I am personally leaning towards using door #2 but I haven't flushed the idea out fully.  Can anyone provide some help?  Especially if there is anything I should be cautious of?

    Thx! 

  • February 25, 2004 at 2:23 pm

    #495677

    Vad4,

    Here's a proc to "put" files:

    --From posting by edk89 on sqlservercentral.com 12/09/2002       

    --Subsequently modified by Bill Nye 9/29/03 to include servername (for other than default instance) and ftp sub directory parameter.

    USE Tempdb

    -- creating the stored procedure for FTP

    IF EXISTS (SELECT name

        FROM   sysobjects

        WHERE  name = N'up_FTPPushFile'

        AND    type = 'P')

        DROP PROCEDURE up_FTPPushFile

    GO

    Create proc up_FTPPushFile

    @file_to_push varchar(355),

    @ftp_to_server varchar(355),

    @ftp_subdir 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 5 records:

    --1) login

    --2) password

    --3)  sub directory

    --4) ftp command and file to push

    --5) exit command

    declare @sql varchar(455), @cmd varchar(355), @batch_ftp varchar(355), @ret int

    declare @servername varchar(50)

    set @servername = @@servername

    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 +' /S '+ @servername + ' /T /c'

    Create table ##temp_ftp_bat(ftp_batch varchar(355), file_to_push varchar(355))

    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 ('cd '+@ftp_subdir, @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(355))

    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

    go

    --to use:

    --use '.' for root directory

    exec up_FTPPushfile 'C:\file.htm', 'FTP.Domainname.com','.','user', 'pwd'

  • February 25, 2004 at 3:44 pm

    #495712

    The problem with the built in ftp client is that it CAN'T handle Passive FTP which sometimes is required!  


    * Noel

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply