Copying a backup from one server to another

Question

Hi

Using SSIS and a File System Task how can I ascertain which is the newest SQL Server backup (file etc)  based upon the following nomenclature.

I then wish to copy that file to another server (which the File System Task) should be able to do.

Adventure_works_20120802.bak

Adventure_works_20120803.bak

Adventure_works_20120804.bak  --> This is the source that I wish to copy.

Should you have any ideas that you could point me to, then I would really appreciate the help.

regards Steve

Answer 1

Here are the steps you can do to get the latest file from the source folder and process it:

1 - Add 2 variables "@FileName" string, "@FileDate" datetime to the package.

2 - set @FileDate value to 1/1/1900.

3 - Add a "Foreach Container" to get the list files from the source folder

4 - Within that "Foreach Container", add a "Script Task".

5 - In this script task, split the file name to get the date from the file name and check if the date from the file name is greater than @FileDate. If it is then save the file name to @FileName and save the date  to @FileDate. pseudo code will be:

var <filename> = <get file name> 

var <date> = <build date from file name>

if <date> GREATER THAN @FileDate

SET @FileName =  <Filename>

SET @FileDate = <date>

6 -Save the script task

7 -  At the end of loop you will have the latest file name.

Hope this helps-

---

Please mark the post as answered if it answers your question

Answer 2

Please follow below link for solution.

http://microsoft-ssis.blogspot.co.uk/2011/01/use-filedates-in-ssis.html

Answer 3

Thanks ever so much. Will give it a try

Answer 4

Thanks ever so much. I shall give it a try