Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2010
    Posts
    9

    Unanswered: Adding Check for Existence of File

    Hi,

    We have an ActiveX Task inside a DTS package that sets up variables (see below). We simply want to check for the existence of the ,,sDeltaF” value, if the file exists then proceed, if the file does not exist, then exit function immediately. If the check fails, we want to exit the entire ActiveX Task and therefore the DTS Package must fail.

    Here’s some code I think will work; questions to help with:

    i. Can Function Main2() simply follow the End Function of Main()?

    ii. Is this line correct: set sDeltaF = “sHRPath” & “sDeltaFileName”

    a. It should result in \\myserver6\files\DeltaFile20100913.txt

    iii. Should this line be Main2 instead: Main = DTSTaskExecResult_Success

    iv. sConfigINIPath should require no changes





    ‘ Pkg Full (Delta File Exists -1)

    Option Explicit



    Function Main2()

    Dim oFSO, sDeltaF

    ‘ Set the name of the file from the variables already set

    set sDeltaF = “sPath2” & “sDeltaFileName”

    set oFSO = CreateObject(“Scripting.FileSystemObject”)



    ‘ Check for file and return appropriate result

    If oFSO,FileExists(sDeltaF) Then

    Main = DTSTaskExecResult_Success

    Else

    Main = DTSTaskExecResult_Failure

    Exit Function

    End If

    Set oFSO = Nothing

    End Function





    ‘*******BEGIN FUNCTION MAIN()**************

    Function Main()

    Dim oComputerName

    oComputerName = DTSGlobalVariables("ComputerName").value

    'msgbox "Computer Name is " & oComputerName

    Dim sConfigINIPath

    Dim sHRPath

    Dim sHRFeedDuplicatePath



    if oComputerName = "myserver6" then 'Computer is production

    sConfigINIPath ="\\myserver6\System.ini"

    sPath ="\\myserver5\files\"

    sPath2 =”\\myserver6\files\”

    else

    dim msg

    msg = "You are running this on a machine not recognized by the system. "

    msg = msg & " Run this package from a Terminal Server instance. "

    msg = msg & "This program is terminating."

    Msgbox msg

    Main = DTSTaskExecResult_Failure

    Exit Function

    end if



    DTSGlobalVariables("INIPath").Value = sConfigINIPath



    Dim sFullFileName

    sFullFileName = "FullFile" & DatePart( "YYYY",Now() ) & DatePart( "m",Now() ) & DatePart( "d",Now() ) & ".txt"

    Dim sDeltaFileName

    sDeltaFileName = "DeltaFile" & DatePart( "YYYY",Now() ) & DatePart( "m",Now() ) & DatePart( "d",Now() ) & ".txt"



    DTSGlobalVariables("INIPath").Value = sConfigINIPath

    DTSGlobalVariables("FullFileName").Value = sPath & sFullFileName

    DTSGlobalVariables("DeltaFileName").Value = sPath & sDeltaFileName

    DTSGlobalVariables("Path2").Value = sPath2



    Dim oPKG

    Set oPKG = DTSGlobalVariables.Parent

    Dim oAssignments

    Dim oAssignment

    ' DTSTask_ DTSDynamicPropertiesTask_1 is the task described as the Properties From INI File task

    Set oAssignments = oPKG.Tasks("DTSTask_DTSDynamicPropertiesTask_1").C ustomTask.Assignments

    For Each oAssignment In oAssignments

    oAssignment.SourceIniFileFileName = sConfigINIPath



    Next

    Main = DTSTaskExecResult_Success

    End Function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is wrong:
    Code:
    If oFSO,FileExists(sDeltaF) Then
    It should be a . not a ,

    This is wrong:
    Code:
    set sDeltaF = “sPath2” &  “sDeltaFileName”
    Remove the quotation marks. You also need to scope these variables where they can be seen by Main and Main2. To be honest, I don't see why the code in Main2 can't be included in Main.

    I have not scripted DTS packages for many years - this is a deprecated technology - so I am very rusty.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Posts
    9

    Wink Adding Check for Existence of File...continued

    Thanks.

    I thought the code could go under Main() as well. Where is appropriate? Just insert it all before the End Function (last) line?


    I have a new requirement: in addition to checking for the existence of the file, if the file exists, I want to know that it is greater than 2K. Because sometimes the file will be greater with headers and no data with the file always less than 2k. How could this be coded in this example?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would get your first requirement working first. But to do that you'll need the size property of the file object.
    FileSystemObject Basics
    Size Property
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File

    Thanks for the links. Very helpful.

    Okay, if we focus on the first requirement...

    If I put these lines before the End Function of Main(), it should be okay, right?

    Dim oFSO, sDeltaF
    ‘ Set the name of the file from the variables already set
    set sDeltaF = sPath2 & sDeltaFileName
    set oFSO = CreateObject(“Scripting.FileSystemObject”)

    ‘ Check for file and return appropriate result
    If oFSO.FileExists(sDeltaF) Then
    Main = DTSTaskExecResult_Success
    Else
    Main = DTSTaskExecResult_Failure
    Exit Function
    End If
    Set oFSO = Nothing


    I'm in theoretical phase, don't have access to the server yet.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    this is superfluous:
    Exit Function

    No development server? Really?

    Also, please could you wrap code in the [ code] tags? It makes things much easier to read.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File...continued

    Sure can.

    If I put these lines before the End Function of Main(), it should be okay, right?

    Code:
    Dim oFSO, sDeltaF
    ‘ Set the name of the file from the variables already set
    set sDeltaF = sPath2 & sDeltaFileName
    set oFSO = CreateObject(“Scripting.FileSystemObject”)
    
    ‘ Check for file and return appropriate result
    If oFSO.FileExists(sDeltaF) Then
    Main = DTSTaskExecResult_Success
    Else
    Main = DTSTaskExecResult_Failure
    Exit Function
    End If
    Set oFSO = Nothing

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah, that looks ok. Again, you can remove the Exit Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File...continued

    Awesome. Thanks again.

    Now on to the second requirement. Here's the same code updated with the check on the file size. When the file is created with headers only, no data, it is always 277 bytes. My question is if the <300 represents bytes and if the new lines will work so that if a file exists but it is less than 300 bytes, will the code exit the DTS package?

    Here's the code:

    Code:
    ‘  Pkg Full (Delta File Exists -1)
    Option Explicit
    Dim oFSO, sDeltaF,oSize
    ‘ Set the name of the file from the variables already set
    set sDeltaF = sPath2 &  sDeltaFileName
    set oFSO = CreateObject(“Scripting.FileSystemObject”)
    set oSize = CreateObject(“File”)
    
    ‘ Check for file and return appropriate result
    If oFSO.FileExists(sDeltaF) Then
                 Set oSize = oFSO.GetFile(“sDeltaF”)
    	     If oSize < 300 Then
    	     Main = DTSTaskExecResult_Failure
                      Else 
    	     Main = DTSTaskExecResult_Success
                      Exit Function
                      End If
    Else
    	Main = DTSTaskExecResult_Failure
    End If
    Set oFSO = Nothing

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    If oFSO.GetFile(“sDeltaF”).Size < 300 Then
    No need for another variable and you need to reference the size property.

    I believe it will, and again the Exit Function is unnecessary and unwanted.
    As said, I am not really up to coding DTS on web pages so the proof of this package will be in the running.

    You know of course you will need change all your apostrophes and quotation marks right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File...continued

    Still, very grateful for your expertise.

    So, the new code looks like this. Not sure what you mean by change quotation marks? Everything I've seen used double-quotes (and those comment characters are supposed to be single quote marks, isn't that right?). Also not sure what you mean by having to reference the Size property?

    So if successful, [If oFSO.GetFile("sDeltaF").Size < 300 Then] will both check for the existence of the file and that the file size is greater than 300 bytes? Cool.


    Code:
    '  Pkg Full (Delta File Exists -1)
    Option Explicit
    Dim oFSO, sDeltaF
    ' Set the name of the file from the variables already set
    set sDeltaF = sPath2 &  sDeltaFileName
    set oFSO = CreateObject(“Scripting.FileSystemObject”)
    
    ' Check for file and return appropriate result
    If oFSO.GetFile("sDeltaF").Size < 300 Then
    	Main = DTSTaskExecResult_Failure
    Else
    	Main = DTSTaskExecResult_Success
    End If
    Set oFSO = Nothing
    Set sDeltaF = Nothing
    End Function

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In all previous code, your apostrophe was ASCII(145). In your last set of code it is ASCII(9), which is correct.
    However, the quotes around Scripting.FileSystemObject are incorrect - you need to use the same as around sDeltaF. However, the quotes around sDeltaF should not be there!

    In your code post #9 you never actually referenced the Size property of the file - have a look at your code again.

    Why did you remove the FileExists check? If the file does not exist then your new code will error rather than fail gracefully.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File...continued

    Guess I'll stop writing this in Word.

    I got the quotes worked out, thanks.

    Here's the new code. Still not sure how to reference the size property.

    Code:
    ' Pkg FullHDFeed (Delta File Exists -1)
    Option Explicit
    Dim oFSO, sDeltaF
    
    ' Set the name of the file from the variables already set
    set sDeltaF = sHRPath &  sDeltaFileName
    set oFSO = CreateObject(“Scripting.FileSystemObject”)
    
    ' Check for file and return appropriate result
    If oFSO.FileExists(sDeltaF) Then
        If oFSO.GetFile(sDeltaF).Size < 300 Then
            Main = DTSTaskExecResult_Failure
        Else
            Main = DTSTaskExecResult_Success
        End If
    End If
    
    Set oFSO = Nothing
    Set sDeltaF = Nothing
    Last edited by pootle flump; 09-16-10 at 12:38.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have highlighted in your post in red where you reference the Size property. It was in post #9 that you did not reference it (look carefully - you'll see that you set oSize to the file itself, not the size of the file).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2010
    Posts
    9

    Adding Check for Existence of File...continued

    That is that then the reference is corrected. Awesome. Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •