Tuesday, December 18, 2012

Selective File Archiving using SSIS

File Archiving is one of the major maintenance tasks in every database. There are numerous occasions where data transfer happens in the form of  files (xls,csv etc) so there has to be an efficient archiving mechanism as a part of DB maintenance strategy. This blog gives you a way to do selective archiving of files based on file attribute values.
The below illustration shows a package designed to archive files over 40 KB from a remote folder. The package looks like below

The package consists a for each loop to loop through files in a remote folder. The script task inside retrieves the attribute values for the fetched file in each iteration. This value is used in filter criteria for identifying the files to be archived. In our above example we retrieve file size attribute and identify large files > 20 KB using retrieved value. The script task will have the following code

The code just involves retrieving filesize using FileInfo class and storing it in a variable. This will be used later in the filter for archive step. We make use of Expression and Constraint option for the precedence constraint as follows. The condition @FileSize > 40 filters only files more than 40 KB for archiving.

The File System Task will be as follows

It archives the selected files from remote folder to path specified by archive file path variable. All the variables can be identified as configurations and passed values from outside during package invocation either programatically or from a job.