Run DTS Package only 10 times a year

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 2000 Forums

SQL Server Development (2000)

Run DTS Package only 10 times a year

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-18 : 09:17:01

Sonny writes "I have created a dts package that ftp's a fixed lenght file from an ftp site then puts the data from the file into a holding table then runs a sp to put the data in the tables it needs to go in. The package runs great. But this package only needs to run about 10 times a year. This is when they put a new file on the ftp site then I get it and use the data to update some tables. How can I check the file to see if it is the same file as the last time it ran, and if it is stop the package, or if it is a different file, run the package. The file name is always the same. I thought about checking the files date but do not know how to do this from the ftp in dts."

jeroena
Yak Posting Veteran

76 Posts

Posted - 2003-04-18 : 10:12:23

Maybe you can just transfer the file via FTP. Then use an ActiveX Script Task to create two file objects to check if the date of the new file differs from the old one. (something with FileSystem). Return Succes if the the date is different and end the DTS run on failure. Just an idea, haven't tested anything...

Jeroen.

Edited by - jeroena on 04/18/2003 10:15:06

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-18 : 10:33:02

I wouldn't use dts for any of this - but I'm a bit strange in this.

You have steps
check for new file
ftp from remote site
import file to staging table
run SP to merge data.

Why not put these as separate steps in a scheduled job as stored procedues - fail the first step if the file is the same.
Or you can do it all in one SP - exit if the file is the same or has the same datestamp as the last one transferred.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources