Loading Multiple Files From a Directory

  • satrigney

    SSC Eights!

    Points: 896

    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!

  • smcmickens

    SSC Rookie

    Points: 39

    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: 119694

    Sounds like a permission problem maybe?

    Andy

  • satrigney

    SSC Eights!

    Points: 896

    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")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSExecuteSQLTask_1")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSExecuteSQLTask_2")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSDataPumpTask_1")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSDataPumpTask_3")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSDataPumpTask_4")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSDataPumpTask_2")

    oStep.ExecutionStatus = DTSStepExecStat_Completed

    SET oStep = oPKG.Steps("DTSStep_DTSActiveScriptTask_7")

    oStep.Execute

    End If

    Any ideas??

    smcmickens - There is an article about doing this on Darren Green's SWYNK site..

    http://www.swynk.com/friends/green/dtsdirloopstep.asp

  • smcmickens

    SSC Rookie

    Points: 39

    Thanks for the link. It looks like what I was looking for.

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

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