Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Exclamation Unanswered: Execute Process Task & FTP -- NEED HELP 911

    Guys!

    Need some help on to figure out some solution for my problem. I have Execute process task in my DTS to execute the BAT file to connect to FTP site using simple DOS commands. All, i need, after executing the BAT file using Execute process task, need to send an Email to the administrator about the status of execustion.

    The problem occured here, I specifically, take out the password field in the BAT file to connect to the FTP site. In general, the batch file could not connect to FTP site, since i taken out the password field in the BAT file. And after that, in DTS package i tried executing the Execute process Task seperately, and all i can get the messages "command executed successfully".

    Based upon the status of the execute process task, I am writing a "Not Successful" message in the table. From that, table i am using the stautus to send an Email to an Administrator.

    So Folks please help me out, how can I sql server know the status of the Execute Process task status, whether is it Successful or not.

    In my case, this execute process task simply says successful, een though the BAT file could execute the command.

    Guys help me out and I am in a little presure to accomblish this ASAP.

    Baski.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, the bat file did complete successfully..it didn't blow up.

    It just didn't do what you wanted...

    Why not echo the results of the bat to a file and interogate that?

    Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

    Just an idea...

    But why use bcp and a sproc?

    That's what I would do...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Hey Bratt,


    I totally understand your way of thought and appriciate it. Great, Since, the DTS which we wrote was completely huge. So any thoughts about error handlimg or pass the error code for Execute process task.

    Is there anyway, can i able to check the status of the Execute process task as well as BAT file, that called inside the Execute Process task.

    Thanks for your time Guys.

    Thoughts are most welcome.

    Baski.

    Originally posted by Brett Kaiser
    Hey, the bat file did complete successfully..it didn't blow up.

    It just didn't do what you wanted...

    Why not echo the results of the bat to a file and interogate that?

    Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

    Just an idea...

    But why use bcp and a sproc?

    That's what I would do...

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check inside the bat file

    f errorlevel 1 goto ERROR
    if errorlevel 0 goto SUCCESS

    :ERROR
    echo transfer failed
    goto ENDFTMS

    UCCESS
    echo transfer successful
    goto ENDFTMS

    :ENDFTMS
    echo batch program complete
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    5

    Unhappy

    Bratt,

    Good to hear your thought on this. Well, I believe you aware that, the DOS BAT file is executing in FTP location. Also, is there any thought about, CHECKING THE FILE EXISTENCE IN THE FTP LOCATION USING BAT FILE.

    Once, i figure out if the xyz.abc file is located in the FTP file, I can pass the parameter or return code as 0/1 to Execute process task to find whether the TASk is successful or not.

    Make sence? GUYS IS THERE ANY THOUGHT TO CHECK THE FILE EXISTENCE IN THE FTP LOCATION & RETURN WITH CODE 0 OR 1 AFTER EXECUTING THE BAT FILE.

    THANKS IN ADVANCE GUYS.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, in a sproc

    Code:
    	Delete From Ledger_Folder
    
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 4
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    	Select @Command_String = @FilePath + '\*.*'
    
    -- 	Select @Command_String
    
    --	Insert Into Ledger_Folder exec master..xp_cmdshell @Command_String
    
    	Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 5
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    --	select * from ledger_folder
    
    	Delete From Ledger_Folder_Parsed
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 6
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    	Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
    	Select	  Convert(datetime,Substring(dir_output,1,8)
    		+ ' ' 
    		+ (Substring(dir_output,11,5) 
    		+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
    		, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size 
    		, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
    	  From 	  Ledger_Folder
    	 Where  Substring(dir_output,1,1) <> ' '
    	   And (Substring(dir_output,1,1) <> ' ' 
    	   And  Substring(dir_output,25,5) <> '<DIR>')
    
    	SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @Error_Out <> 0
    		BEGIN
    			Select @Error_Loc = 7
    			Select @Error_Type = 50001
      			GOTO Load_Ledger_Init_sp_Error
    		END
    
    -- Get File For the 1st Month of a Quarter
    	
    	If (   Select Count(*)
    	  	 From Ledger_Folder_Parsed
    	 	Where Substring(File_Name,16,2)= @Month1 And Substring(File_Name,11,4)= @Proof_Year
    
    	    ) = 0
    		BEGIN
    			SELECT @Error_Loc = 8
    			SELECT @Error_Message = 'First Monthly File Not Found.  Check Syntax for File Name. '
    						+ ' Syntax is: ' + @Fn1 + '_yymmdd.txt'
    			SELECT @Error_Type = 50002
      			GOTO Load_Ledger_Init_sp_Error
    		END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2003
    Posts
    5
    Hey Bratt,

    Thanks a Lot and ofcourse, I may not be using the sproc for checking the file existence. Since, we have been trying to upload the file to a MAINFRAME BASED FTP SITE.

    So it would be more welcome, if you provide some solution or step based on the DOS mode or command line statements.

    Thanks in Advance.

    Baski.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    You can also use the ActiveX Script task in dts to test for file existence using the fso object - this would be cleaner. Why don't you use the ftp task in dts rather than using a batch file ?

  9. #9
    Join Date
    Nov 2003
    Posts
    5
    The Problem is you may not be using the FTP task for upload or download function in Mainframe based FTP site. SO i have to use the DOS version of file existence as BAT file in Execute process task.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •