Answered by:

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.

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.

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?

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.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.