none
How to copy only newest file via SSIS

    Question

  • Hi all -
    I have an SSIS package that checks a network share and has been copying over a file to import into our SQL 2005 database.

    I am trying to find a way within SSIS to only get/load the latest file, based on the date and time of the file.  I don't see a way to designate something like, "use only the newest file" in the File System Task. 

    And I cannot use some 3rd party tool or utility, it has to be something native to SSIS. 

    Has anyone been able to do this?

    Thanks
     - will
    will
    Thursday, March 26, 2009 6:33 PM

Answers

All replies

  • If the date and time of the file is part of the file name, you can use a "Foreach Loop Container" to go through the list of files in the folder to find the latest file.  The name of the latest file would be stored in a string variable that is linked to the ConnectionString property of your file connection.
    Thursday, March 26, 2009 7:07 PM
  • Thanks for the speedy response.

    Yes, the file does have a datetime stamp as part of the file name, but how do I tell the FileSystemTask to only get the latest file?  I have set up a Foreach Loop container before, but it just grabbed any file that was in the folder and not just a specific one.  I can't figure out how to ONLY get the latest one.  Is there a way to do this?

    thanks
     - will
    will
    Thursday, March 26, 2009 7:13 PM
  • 1. The Foreach Loop Container will go through all the files in the folder.  Within the container, create a script to determine in the filename (based on the datetime stamp) is the newest file.  Write the file name to a string variable.

    2. In your file connection, click on the Expressions ... in the property.  Set the connection string to reference the string variable that would contain the name to the newest file.

    3. Connect the File System Task to the Foreach Loop Container.  Set the SourceConnection of the File System Task to the file connection from step 2.

    I haven't work with File System Task for a while.  You might need to repeat step 2 and 3 to set the DestinationConnection property.

    HTH

    Thursday, March 26, 2009 7:32 PM
  •  Ahh, okay.  I haven't use the Script task yet, even though I have been building SSIS packages since SQL 2005 was released. 

    Your response makes sense and it looks like I will now have some time to figure out how to use the Script task to read the filename and then set my variable to the newest one.

    Thanks again for your help.
     - will
    will
    Thursday, March 26, 2009 7:49 PM
  • Here is a blog post that does something similar:

    http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

    in this example I use the system date as reference.
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Friday, March 27, 2009 1:17 AM
  • something very near to it:


     http://sqlserversolutions.blogspot.com/2009/03/getting-daily-feed-in-ssis.html

    thanks
    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
    Friday, March 27, 2009 6:51 AM
  • Thanks so much.  this is great.
    will
    Wednesday, April 08, 2009 12:00 AM
  • A different approach
    This example uses the actual last-modified or creation date of the file: http://microsoft-ssis.blogspot.com/2011/01/use-filedates-in-ssis.html

     

    Saturday, January 15, 2011 7:30 AM