Using DTS to Detect and Process a File

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Bruce Szabo » Using DTS to Detect and Process a File

12 posts, Page 1 of 2

1 2 »»

Using DTS to Detect and Process a File

Rate Topic

Display Mode

Topic Options

Author

Message

bmsjr

Posted Sunday, June 30, 2002 12:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bszabo/dtsfilearrival.asp

Bruce Szabo, MCSE+I, MCDBA, MCSD

Post #5155

Andy Warren

Posted Saturday, July 06, 2002 9:27 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Good use of the wshell object. This is cleaner than the "shell" command used in VB which normally runs asynchronously. I see in a couple places you're declaring more than one instance of the file system object...why? Also, I think you're using the DONE file as an indicator that the copy of the zip file to the folder is complete?

Note to Readers: Pkzipc is the command line version of PKzip and is not installed on your server by default!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #36628

bmsjr

Posted Sunday, July 07, 2002 7:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6

Confusion was the only reason I had more than one instance of the filesystemobject. It was poor programming on my part and I missed it in the original article.

You are correct, the .DONE file was used as a marker file to signal the transfer of the .ZIP file was complete.

Another good point about pkzip being the command line utility. You can purchase it from pkware the url is http://www.pkware.com.

Bruce

Bruce Szabo, MCSE+I, MCDBA, MCSD

Bruce Szabo, MCSE+I, MCDBA, MCSD

Post #36629

Steve Jones - SSC Editor

Posted Monday, July 08, 2002 1:33 PM

SSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722

Nice article, however I think some of the lesser experienced might like some more commentary on how it works. For me, however, its nice to see how someone else does this.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #36630

bmsjr

Posted Monday, July 08, 2002 5:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6

Thanks Steve, I am always accused of being too long winded and complex in my e-mails. :) I try to present some scripts that I find useful and do a little more annotation without getting too involved. I will keep it in mind in the future.

Bruce

Bruce Szabo, MCSE+I, MCDBA, MCSD

Bruce Szabo, MCSE+I, MCDBA, MCSD

Post #36631

sreeram68

Posted Monday, July 08, 2002 10:01 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 02, 2006 1:39 AM
Points: 11, Visits: 1

very nice artcile and explains clearly the FileSystemobject usage and it susefulness

Post #36632

bsluman

Posted Wednesday, November 20, 2002 4:06 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, July 05, 2007 5:42 AM
Points: 31, Visits: 1

I am having problems with this procedure. I get an error on line 28 of the "Check for .DONE file" component. Where would I find this .DONE file? The line it is erroring on is:
master.WriteLine objFSOFile.name
The error is:
Object Required: 'objFSOFile'

Post #36633

bmsjr

Posted Wednesday, November 20, 2002 6:27 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6

Have you modified the code at all? The .Done file is going to be located in the ftp directory. If you right click on the DTS package one of the global variables specifies the ftp directory.

Bruce Szabo, MCSE+I, MCDBA, MCSD

Bruce Szabo, MCSE+I, MCDBA, MCSD

Post #36634

bsluman

Posted Thursday, November 21, 2002 8:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, July 05, 2007 5:42 AM
Points: 31, Visits: 1

Ok, I am looking at the .DONE component and I think that I might see something wrong with it. Correct me if I am wrong:
In line 33 or so you check for the ".DONE" tagged to the end of the file.
if ucase(right(objFSOFile.name,4)) = "DONE" then strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-5))
So, say we have a file named 20021212.zip.DONE. I am assuming that this is what you are looking for. (I still do not know how the .DONE gets added in the first place)
So, you set the variable filename base equal to the file name minus 5 which effectively eliminates the .DONE.
This gives us 20021212.zip as the filenamebase.
But, on line 41 you write the following:
If fso.FileExists(strFTPDir & "\" & strFileNameBase & ".zip")
This file will not exist because it is still called:20021212.zip.DONE
We have not removed the .DONE from the actual filename. Even if we had then the .zip is still there and we would be looking for 20021212.zip.zip

Post #36635

bmsjr

Posted Friday, November 22, 2002 4:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:06 AM
Points: 171, Visits: 6

Now I see your issue. :) I have the sending company send me two files. A large .Zip file and a small .DONE file. I do not and can not start processing the .Zip file untill it is fully uploaded. For this reason the sending company sends me a .DONE file to signal the first file copy is complete. I have FILE1.DONE and FILE1.ZIP. I really only needed to know the ZIP arrived so I extract the base filename from the .DONE file by subtracting the 5 characters. I must not have mentioned the two file system I have. Sorry. Does that help?

Bruce

Bruce Szabo, MCSE+I, MCDBA, MCSD

Bruce Szabo, MCSE+I, MCDBA, MCSD

Post #36636

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions