DTS package - need to check if file exists 1st

  • I want to create a DTS package that creates calls in our CRM database based on information within a file that I get from another application. My first check within the package is to check and see if this file exists within a particular folder. Obviously this would be the first step in my package and the package would move to step 2 if the file does exist. If not, it would stop execution. What would be the best way to check whether a file exists or not within a certain folder on our network within a DTS package?

    Regards,

    Bessebo

  • I prefer to use a vbscript task to determine if a file exists !

    Try this :

    Option Explicit

    Function Main()

    Dim objFSO

    set objFSO = createObject("Scripting.FileSystemObject")

    If objFSO.FileExists("O:\MyFolder\MySubFolder\File.txt") Then

    Main = DTSTaskExecResult_Success

    Else

    Main = DTSTaskExecResult_Failure

    End If

    Exit Function

    ErrorExit:

    Main = DTSTaskExecResult_Failure

    End Function

    Andy P


    Andy P

  • Andy,

    That is awesome. Thanks for the help.

    Regards,

    Bessebo

  • Andy,

    I just incorporated that code into a package and it worked like a charm. I now want to be able to rename the file to I was checking the existence of to a different name or to a different folder. I have been looking into the vbscript references for how to do this and cannot find it yet. Any ideas?

    Thanks,

    Bessebo

  • I also do this I use the commands (in the relevant place) :

    objFSO.CopyFile "src-Path\filename", destination-Path\filename",true

    [where last boolean is for overwrite option]

    and use the same folder

    and then

    objFSO.DeleteFile "Path\FileName"

    to delete the original.

    Hope this helps.

    Andy P

    quote:


    Andy,

    I just incorporated that code into a package and it worked like a charm. I now want to be able to rename the file to I was checking the existence of to a different name or to a different folder. I have been looking into the vbscript references for how to do this and cannot find it yet. Any ideas?

    Thanks,

    Bessebo


    Andy P


    Andy P

  • Andy,

    Thanks. Do you have a copy of an Acgive Script Task where you perform this logic? If so could you forward it along or email me directly at bbessette@sensitech.com.

    Regards,

    Bessebo

  • I'll see what I can dig up....I know that most of my scripts that use the FSO are broken in 2 and exist either side of sql tasks. Plus they use the global variables for passing data (filenames etc) between them. (I will have to remove all paths etc but you should be able to follow the flow). I'll try and get around to it this morning.

    Andy P


    Andy P

  • Appologies for delay.......exceptionally busy at the moment.......

    I cannot find (quickly) a rename example.

    The following example uses the global-variables to store the working names.

    NOTE : The scripts are separate vb tasks within a dts. global variables are used to pass values accross the tasks.

    I use the move to rename when transient !

    Hope this helps.

    Regards

    Andy P

    '***********************************************************************************************************************

    '1.Determine if there is anything to do and set global dts variables

    '***********************************************************************************************************************

    Option Explicit

    Function Main()

    Dim objFSO

    Dim objFolder

    Dim ThisFile

    Dim objFile

    Dim strWorkingFolder

    Dim strWorkingFileName

    dim strArchiveFileName

    Dim intTotalXMLFiles

    'Get working folder location

    strWorkingFolder = DTSGlobalVariables("WorkingFolder").value

    'Get working filename

    strWorkingFileName = DTSGlobalVariables("WorkingFile").value

    'Initialise local variables

    intTotalXMLFiles = 0

    'See if there are any files to process If there are then get the name of a file to process and store the count of files that need to be processed

    set objFSO = createObject("Scripting.FileSystemObject")

    set objFolder = objFSO.GetFolder(strWorkingFolder)

    For Each ThisFile In objFolder.Files

    If LCase(Right(ThisFile.Name, 3)) = "xml" Then

    Set objFile = ThisFile

    intTotalXMLFiles = intTotalXMLFiles + 1

    strArchiveFileName = ThisFile.Name

    End If

    Next

    'If we have anything to do then store the name and rename to the working filename

    If intTotalXMLFiles > 0 Then

    DTSGlobalVariables("TempArchiveFilename").value = objFile.Name

    objFile.Name = strWorkingFileName

    Else

    End If

    'Always set global counter so zero files can be catered for

    DTSGlobalVariables("FilesToProcessCount").value = intTotalXMLFiles

    Set objFSO = Nothing

    Set objFolder = Nothing

    Set objFile = Nothing

    Main = DTSTaskExecResult_Success

    Exit Function

    ErrorExit:

    Main = DTSTaskExecResult_Failure

    End Function

    '***********************************************************************************************************************

    '4a.(If 3 fails) Restore the filename and move into the error folder

    '***********************************************************************************************************************

    Option Explicit

    Function Main()

    Dim objFSO

    'Rename back to orignal name and archive the working file

    set objFSO = createObject("Scripting.FileSystemObject")

    objFSO.MoveFile DTSGlobalVariables("WorkingFolder").value & DTSGlobalVariables("WorkingFile").value, DTSGlobalVariables("ErrorFolder").value & DTSGlobalVariables("TempArchiveFilename").value

    Set objFSO = Nothing

    Main = DTSTaskExecResult_Success

    Exit Function

    ErrorExit:

    Main = DTSTaskExecResult_Failure

    End Function

    Andy P


    Andy P

  • Thanks Andy for the help..

    Regards,

    Bessebo

Viewing 9 posts - 1 through 8 (of 8 total)

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