Loading Multiple Files From a Directory

Question

Loading Multiple Files From a Directory

satrigney

SSC Eights!

Points: 926
More actions
September 17, 2001 at 12:57 pm

#157785

I have a DTS Package written that will loop through a directory and load all files that are named a certain way. After it has loaded the file, it archives it to an archive directory. I also have in logic that if the file we are working with is a zero byte file, to set all of the steps ExecutionStatus to Completed, and then execute the last step, which is to archive the file.
My problem is that it works perfectly from DTS Designer, both on my machine and the server desktop. But scheduled as a job it fails when we get the zero byte file.
Does anyone have any suggestions as to what might be happening, or how we can troubleshoot it?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

smcmickens SSC Rookie Points: 39 More actions · Answer 1

I have not run into that, but I am interested as to how you went through a directory search to load multiple files. I need to create a DTS that scans a directory for multiple files, imports them into a table and then deletes the files once the job is finished. I would be very interested in any ideas you may have.

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

Sounds like a permission problem maybe?

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

satrigney SSC Eights! Points: 926 More actions · Answer 3

It couldn't possibly be a permissions problem, because it only happens with zero byte files, and we do the same processing with non-zero(data containing) files. The only difference is the process in which it gets to the archive step. With the zero byte files, I set all of the Execution Status's to completed, and then execute the step within the ActiveX Code. With the data containing files, it executes when all steps in between execute.

I'm in SQL 7... here is a snippet of code.

If ObjFile.Size > 0 Then

'Execute Disabled Step

' Set Text File Property, which file to objFile

DTSGlobalVariables("gvsCurrentFile") = strFileName

oPackage.Connections("Text File (Source)").Properties("DataSource").Value = strFileName

oPackage.Tasks("DTSTask_DTSDataPumpTask_1").Properties("SourceObjectName").Value = strFileName

Exit For

Else

'No File Size... do something

DTSGlobalVariables("gvsCurrentFile") = strFileName

Dim oPKG

Set oPKG = DTSGlobalVariables.Parent

SET oStep = oPKG.Steps("DTSStep_DTSActiveScriptTask_1")