How to copy only newest file via SSIS
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 onlyget/load the latest file, based on the date and time of the file. Idon't see a way to designatesomething like, "use only thenewest file" in theFile System Task.And I cannotuse some 3rd party tool orutility, it has to be something native to SSIS.Has anyone been able to do this?Thanks- willwill
March 26th, 2009 9:33pm

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.
Free Windows Admin Tool Kit Click here and download it now
March 26th, 2009 10:07pm

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- willwill
March 26th, 2009 10:13pm

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
Free Windows Admin Tool Kit Click here and download it now
March 26th, 2009 10:32pm

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.- willwill
March 26th, 2009 10:49pm

Here is a blog post that does something similar: this example I use the system date as reference.Rafael Salas | Dont forget to mark the post(s) that answered your question
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2009 4:17am

something very near to it: Kumar, MCTS, India,
March 27th, 2009 9:51am

Thanks so much. this is great.will
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2009 3:00am

A different approach This example uses the actual last-modified or creation date of the file:
January 15th, 2011 2:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics