Does Step Failure Cause Entire Job to Fail?

[dpgirl]

I created a DTS package that pulls a file and then puts it on an ftp site using "Execute Process Task Properties". The file transfer is done using FTP Command Line. My problem is that the ftp site requires the password to be changed every month or so (not on a regular schedule). However, the ftp site allows me to rotate between 2 passwords so that if one password doesn't work, then the other must. Rather than constantly updating the password within the DTS Package, I thought it would be wise to set up 2 steps. One step would be hardcoded with one of the passwords and the other step would be hardcoded with the other password.

Here's the parameter for 1st ftp step (with dummy info):

ftps -user:John.Doe -password:ABC12 -s:\\D:\FTPScript.txt eft.abccompany.com

Here's the parameter for the 2nd ftp step (with dummy info):

ftps -user:John.Doe -password:XYZ34 -s:\\D:\FTPScript.txt eft.abccompany.com

I set up the package so that the if the first ftp step failed, then the 2nd ftp step would run.

When I scheduled the job to run, the file is ftp'd correctly (which is the last step), but the job returns a failed message. Assuming there are no other errors, is this because all steps in a package must execute successfully in order for the entire job to be considered successful? If so, can anyone think of a way around this so that I can still hardcode the two ftp passwords in my command line yet still have all the steps execute successfully?

 

[mrdenny]

No. The way that a job that runs a command is setup is that if the job returns an errorlevel value of 0 it's successful. If it returns any other value it's a failure. Check the return value that it's returning and see what's happening.

You can change the value that SQL is looking for in the job step.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpgirl]

mrdenny,

Could you tell me how to change the error level value so that a Process Exit Code of 1 is considered "Successful"?

thanks!

 

[mrdenny]

On the job step change the "Process exit code of a successful command" from 0 to 1.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dpgirl]

Thanks mrdenny. Is there anyway to set it up so that BOTH Process Exit Codes 0 and 1 are considered successful on the same job? Either result is possible depending on whether the first ftp password worked or not.

 

[mrdenny]

Nope. It's a one or the other kind of thing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

Well, that's not actually true.

If you wrap your dos command in a batch file and run the batch file, you can use the batch file to catch the errorlevel returned by the ftp app, and change the errorlevel as needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com