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.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...|||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...|||Check inside the bat file

f errorlevel 1 goto ERROR
if errorlevel 0 goto SUCCESS

:ERROR
echo transfer failed
goto ENDFTMS

:SUCCESS
echo transfer successful
goto ENDFTMS

:ENDFTMS
echo batch program complete|||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.|||Yeah, in a sproc

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_outp ut,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|||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.|||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 ?|||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