Post reply

Getting latest file in a folder

  • June 25, 2008 at 7:52 am

    #187505

    Hi,

    I have two package variables:

    MyFolder

    NewestFile

    I want to create a simple script task to find the newest file in a folder and write it to the NewstFile package variable - does anyone have an example they could post up? My VB and knowledge of the FileSystem model is a bit rusty.

    Thanks very much

  • June 25, 2008 at 8:20 am

    #834019

    hey,

    First off, you want to import the filesystem reference in your scriping task

    Imports System.IO.FileSystemInfo

    should do the trick

    Now you can reference this as following:

    If Not (FileExists(YourFileName)) Then

    ....do some coding here

    Else

    ....do some other coding here

    End If

    Its not a full script, but rather a prod in the right direction

    ~PD

  • June 25, 2008 at 8:32 am

    #834031

    Here's some code I use to change the properties.

    You should be able to use it to just check them. And this does exactly what

    you're looking for.

    Dim f As New IO.FileInfo("C:\Deploy\MyFile.txt")

    If f.Exists Then

    'f.Delete()

    f.CreationTime = Date.Now

    Else

    f.Create()

    'f.CreationTime = Date.Now

    End If

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 25, 2008 at 9:31 am

    #834091

    Thanks guys, I'm making some progress. Here's my code so far:

    Public Sub Main()

    Dim folder As String

    folder = Dts.Variables("MyFolder").Value.ToString

    Dim oFolder As New DirectoryInfo(folder)

    If Not oFolder.Exists Then

    MsgBox(oFolder.ToString & " directory does not exist.")

    Return

    End If

    Dim files As FileInfo() = oFolder.GetFiles("*.csv")

    Dim fi As FileInfo

    Dim fNewest As FileInfo

    For Each fi In files

    If fNewest.Name = String.Empty Then

    fNewest = fi

    End If

    If fNewest.CreationTime < fi.CreationTime Then

    fNewest = fi

    End If

    Next

    End Sub

    I get an error when I execute the script task:

    Object reference not set to an instance of an object.

    I presume this is because fNewest is not set to a file name. Do you know how I'd get round this?

    Cheers.

  • June 25, 2008 at 9:48 am

    #834107

    FileNewest doesn't have to be an object to compare it to the current file. Just store the current file's info in a variable or 2. So like for each file you'll have something like this:

    dim Filenewest as string;

    dim FileNewestDate as date;

    If fi.creationtime > FileNewestDate then

    Filenewest = fi.name

    end if

    something like that... you get the idea. But that doesn't need to be an object. Then if you want to store it in a separate object, create the object once the newest file is actually found and set it to that file. Then you can work with it.

    You may also find some love in the dictionary object. Perhaps using a dictionary object or some other kind of list and you may be able to sort the files by date then.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 26, 2008 at 1:56 am

    #834451

    Thanks for your help crever (and great videos on your site BTW!).

    I've got it working well now, although I'm sure it could be more elegant. Here's my "finished" code:

    Public Sub Main()

    Dim sDirectory As String

    sDirectory = Dts.Variables("MyFolder").Value.ToString

    Dim dirInfo As New DirectoryInfo(sDirectory)

    If Not dirInfo.Exists Then

    MsgBox(dirInfo.ToString & " directory does not exist.")

    Dts.TaskResult = Dts.Results.Failure

    Return

    End If

    Dim filList As FileInfo() = dirInfo.GetFiles("*.csv")

    Dim filInfo As FileInfo

    Dim sNewestFileName As String

    Dim dtNewestDateTime As Date

    For Each filInfo In filList

    If sNewestFileName = String.Empty Then

    sNewestFileName = filInfo.Name

    dtNewestDateTime = filInfo.CreationTime

    End If

    If filInfo.CreationTime > dtNewestDateTime Then

    sNewestFileName = filInfo.Name

    dtNewestDateTime = filInfo.CreationTime

    End If

    Next

    Dts.Variables("MyFileName").Value = Dts.Variables("MyFolder").Value.ToString & "\" & sNewestFileName

    Dts.TaskResult = Dts.Results.Success

    End Sub

    One final question. If the directory is not found (I test for that), how do I return an error message to the Dts.Results.Failure? Any way to do that?

  • June 26, 2008 at 7:48 am

    #834659

    I'm glad you got it working.

    Yeah, firing events in script code is easy...

    DTS.Events.FireError(0, e.Message, "No Folder Found", "", 0)

    You can look up the EXACT syntax, but that's pretty close off the top of my head.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • June 27, 2008 at 9:03 am

    #835411

    What did you have to do to get DirectoryInfo to be recognized? I have a similar application but get an undefined type error on my dim statement for dirInfo.

    I added Imports System.IO.FileSystemInfo and tried adding Imports System.IO.DirectoryInfo but I still get the error.

  • June 27, 2008 at 10:42 am

    #835506

    I just did Imports System.IO

  • June 28, 2008 at 5:48 am

    #835742

    Thanks! That is what I was missing.

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

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