Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using DTS to Detect and Process a File Expand / Collapse
Author
Message
Posted Sunday, June 30, 2002 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Saturday, July 6, 2002 9:27 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #36628
Posted Sunday, July 7, 2002 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, July 8, 2002 1:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
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
Posted Monday, July 8, 2002 5:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, July 8, 2002 10:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 2, 2006 1:39 AM
Points: 11, Visits: 1
very nice artcile and explains clearly the FileSystemobject usage and it susefulness




Post #36632
Posted Wednesday, November 20, 2002 4:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 5, 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
Posted Wednesday, November 20, 2002 6:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, November 21, 2002 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 5, 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
Posted Friday, November 22, 2002 4:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase 12»»

Permissions Expand / Collapse