check for today''s file on ftp server. if exist, then download else, restart job 30 mins later

Question

check for today''s file on ftp server. if exist, then download else, restart job 30 mins later

lara31

SSCommitted

Points: 1802
More actions
July 19, 2007 at 11:50 am

#69031

okay. i have researhced this for two days. i give up!
what i an trying to do is to connect to my ftp server and check for current days' file. by checking date created. of date modified. if it exists, then download file. if not, then restart the job 30 minutes later going through thesame steps, until the file is found.
any ideas?
[font="Comic Sans MS"][/font]It is what it is.

Viewing 13 posts - 1 through 12 (of 12 total)

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

Does the file have a unique name for today's file as opposed to yesterday's file or is it the same name being replaced each day? No way I know of to get info on the file without pulling the file first but there are other ways around it.

John McC Hall of Fame Points: 3410 More actions · Answer 2

Something along these lines?

http://www.databasejournal.com/features/mssql/article.php/1459691

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

lara31 SSCommitted Points: 1802 More actions · Answer 3

thansk for the response. it actually comes over as ddmmyynameoffile.txt. i tried parsing out the date to get the name and use active x and dynamic task in the dts package. it give a "success message" but the file does not come over.

[font="Comic Sans MS"][/font]It is what it is.

John McC Hall of Fame Points: 3410 More actions · Answer 4

How are you downloading the file from the ftp server?

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

post the code you are using to parse the name.

Are you using the ftp task or the execute command task to get your file?

lara31 SSCommitted Points: 1802 More actions · Answer 6

i am suing the ftp task. i know this is weird but i actually just deleted that part of the code this morning .

but the idea,

im MyDate, MyFile1 , Myday ,MyMonth , MyYear, mycday, mycmonth

MyDay = day((now()-1))

If Len( MyDay) < 2 Then

MyCDay = "0"&Myday

Else

Mycday = myday

End If

MyMonth = Month((now()-1))

If Len( MyMonth) < 2 Then

MyCMonth = "0"&MyMonth

Else

MycMonth = MyMonth

End If

MyDate= mycmonth & Right(CStr(Year(Now)),2)

then i set global variable (sourcename).value = filenamemtdate.txt

somthing along those lines.

his is the code below.

[font="Comic Sans MS"][/font]It is what it is.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

if you look at the global variable after you set it does it look right? Also what ftp paramters are you using and what does the ftp script look like?

bob pearman Mr or Mrs. 500 Points: 567 More actions · Answer 8

this might sound silly but try adding 3 semi colons to the end of the file name string (";;;")

Andrew in WV SSCommitted Points: 1655 More actions · Answer 9

You're probably right. I built some DTS packages a couple weeks ago that use a Dynamic Properties Task to read a configuration table that contains the path to some import files used in an FTP task. Initially, I set the SourceFileName property of the task to the file name stored in the table and the FTP step failed. When I looked closely at the default value for the property, it was a semicolon-delimited value that contained the file name (enclosed in single quotes), an empty string (I assume the file attributes, also in single quotes), and the file size. When I changed the output of the query to format the file name with the quotes and semicolons ("'filename.txt';;;"), the task succeeded.

--Andrew

mrpolecat SSCertifiable Points: 7014 More actions · Answer 10

I have found that using the executable task to execute http://ftp.exe with a script file works better than the ftp task. It allows you to do more within the ftp session.

Will1922 SSCertifiable Points: 7189 More actions · Answer 11

I agree with mrpolecat. I can't say that I have used the FTP DTS task very much, but I would say that you could do all of this through a SQL script without using DTS.

I would try a different route and setup a script that pulls the dir of the folder where the file is into a text file. Then you can BCP that file into a table with a single column of CHAR(500) or something like that. Then just do a select on that table to see if any of the filenames are the date that you are looking for. I have done this before and it works fine.

I like DTS, but it sounds like you are making it into a more complex problem than it needs to be.

Hope I helped.

Live to Throw
Throw to Live
Will Summers

lara31 SSCommitted Points: 1802 More actions · Answer 12

i wll try it

thanks

[font="Comic Sans MS"][/font]It is what it is.