FTP Task: Suppress Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

FTP Task: Suppress Error

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-18 : 16:24:27

Is there any way to suppress the whole package from failing when a file is not found while doing an FTP get (other then setting the MaxErrorCount higher)?

My situation is that I know what a file is named but the file may not exist yet on the server. So, I use an FTP task to do a Get on that file. If the file does not exist then the FTP Task increments the error count even if I force the ExecutionResult and/or ExecutionValue.

This wouldn't be a very big deal but I am looping to getting and processing multiple files. It's acceptable that the file does not exist, but I want to be able to have the package actually error in other places. So far, the only thing I can do to stop the package from failing is setting the MaximumErrorCount for the Loop and the Package which isn't going to cut it.

I am hoping there is away around this without having to use a 3rd party FTP Task.

Thanks,

-Ryan

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 16:31:32

Assuming you are using T-SQL and xp_cmdshell?

The why not use cp_cmdshell to read the directory and load the directory to a table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-18 : 17:26:10

I guess I should have stated a little more clearly that I'm using SSIS.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-18 : 17:31:25

X002548 is right, ftp dir will give a list of files under the directory.

Loading that into a sql table, that way you can programaticcaly deal with each file by name.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-18 : 18:01:14

Thanks for the responses and yes there are many ways to get an FTP listing.

But, I was trying to see if there was something that I do not know about in SSIS that will allow me to supress an error and perform some other processing.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-09-18 : 20:10:29

I've done something similar, I had to download a file via FTP called FileMMDDYYYY.txt, this file is usually posted on the vendor's FTP site between Tuesday and Friday, therefore I had to try different file names with different FTP tasks, since the file is named after the day it was posted (One for Tuesday, one for Wednesday, one for Thursday and one for Friday).

I took all 4 FTP tasks and placed inside a sequence container and set the MaximumErrorCount of the sequence container to 4, if the sequence container fails less than 4 times, that means that one of the FTP Tasks ran successfully. You still have to watch and make sure you combine the number of the errors inside of the sequence container with the number of errors on the whole package, so your package can report success/failure correctly.

T-SQL + xp_cmdshell still gives you more flexibility, but the folks here at my company have an issue with enabling xp_cmdshell, which is something we've been debating for the past 2 weeks...

Hope this helps.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-19 : 13:42:37

I don't think it is the best way and will probably end up writing my own FTP Task. But, for those that are interested, I found part of my fix/hack here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=999239&SiteID=1

What I did was to put the FTP Task in a Sequence container (you could use any container). Then on that container added an OnError event handler and added the following code to suppress the error from bubbling up:

    Private vars As Variables

    Public Sub Main()
        Dts.TaskResult = Dts.Results.Success
        Dts.VariableDispenser.LockForWrite("System::Propagate")
        Dts.VariableDispenser.GetVariables(vars)
        vars("System::Propagate").Value = False
        vars.Unlock()
    End Sub

I tried putting it on the FTP Task but it did not work as I thought it would. Apparently, in the FTP Task, if an file is not found it is not a "error" per se, but a validation error. So, you have to trap that error at the parent of the FTP Task. Then I can do my other processing after the FTP Task and if I encounter a "real" error then the package will fail as expected.

EDIT: I forgot to add that it might be possible to capture the Return code from the FTP Task to determine what type of error happened and deal with that. For example, if the logon failed or something other than a file not existing. I still have to investigate this.
-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources