SSIS File System Copy Task, Sample

One of my contracts uses an online backup solution to handle backups. Let's call it 'BackupSoftware'. It freezes I/O on the database files, and it has no way of distinguishing between old and new backup files. Basically, it attempts to collect ALL files every time it runs, and then fails due to storage limitations.  

I decided to alter my database maintenance routine to work around this problem.  The SQL backups were performed in the last step of the existing routine, and I decided I'd need two more steps to ensure the backup client only targets one day of files, at any time:

  New steps to maintenance job:

    1) Delete any/all files in NewDirectory

    2) Copy the most current .bak/.trn files into this directory

I wanted to keep things simple, and just add another step to my current SQL Server Agent maintenance job, so I decided to use SQL Server Integration Services.


First, I developed this statement to find the most current .bak files, and any associated .trn files:  Most recent backup files

It works great to find them, but how do we then COPY those files into the NewDirectory?  I'm a big fan of keeping it in SQL, so I chose the SSIS File System Copy task, which is written to manipulate files and directories in the file system.   See here, File System Task.

Let me provide a little detail on the package, and then I'll let you take it from there.  You can download the package here, and test it out yourself: CopyFiles.dtsx

These are the components in the control flow:

  One File System Delete directory content task, 'NewDirectory Purge'

  One SQL Task, 'Identify Current Files'

  One File System Copy task, 'Copy Files to NewDirectory', which resides in a Foreach Loop Container

The package has three variables:

  target_directory, string, C:\MSSQL\Backup\NewDirectory\

  physical_device_name, object, System.Object

  current_file, string, 'MyFile.xyz'  (no kidding, that is what it is)

The Most recent backup files statement is the SQLStatement in my SQL Task.

The SQL Task editor has no parameter mapping (because there are no input parms).
The SQL Task Editor result set, Result Name = 0, Variable Name = User::physical_device_name.

The Foreach Loop enumerator is Foreach ADO Enumerator, the configuration has User::physical_device_name as the ADO object source variable, and 'Rows in the first table' as the enumeration mode.  The User::current_file is defined within the Foreach Loop variable mappings as variable, Index = 0.

The File System Task in the Foreach Loop is Operation = Copy File.
IsDestinationPathVariable = True, the DestinationVariable = User::target_directory.
IsSourcePathVariable = True, the SourceVariable = User::current_file.

Initally my File System Task errored with this:
    'Variable "current_file" is used as a source or destination and is empty'

This was because my current_file variable had no value, and didn't know how to get started.  I assumed it was going to get its values from the Foreach Loop, and didn't think anything else was necessary.  When I changed it to 'MyFile.xyz', the validator was happy enough to start going through the loop.  Didn't matter what the value was, because once it started, it got the real filenames, and kept moving through the loop. 

I hope my details are in the right order, and make sense.  There are good annotations in the package, so it may be best just to open it up and take a look.  I think this is a very simple, yet effective way to manipulate the file system, through SSIS (SQL Server Integration Services).  


Please check it out, and let me know if you have any questions or problems.  Please also let me know if I can provide more assistance on this, or any of your other SQL Server projects.

-Rebecca