File Properties Task

This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.

Download the Fully Functional Product for Free


Task Configuration
See Larger Image of Estimation
After dragging the task over, you can see a list of exposed properties for the file. First, specify the connection manager or the variable that contains the file name that you wish to evaluate. Then, map whichever properties you wish to read from the file to a variable in SSIS. Some of the properties exposed are:
  • File Creation Date and Time
  • File In Use
  • Last Access Date and Time
  • Last Modified Date and Time
  • File Read Only
  • File Hidden
  • File Name
  • File Extension

Use Cases

This task compartmentalizes and simples what would be very complex scripting inside a Script Task into a simple to use task user interface with no coding experience needed. Here are a few of the use cases that you can use the File System Task for.

File Archival

Files have a tendency to build up on a SQL Server. Maintenance plan steps fail often times, leaving backups behind or extract files are never removed. You can use the File System Task to quickly develop a package to read the creation date or the last accessed date of a file and move or remove it.

To accomplish this, first drag over a For Each Loop Container and configure it to point to the directory to loop through. You can also setup the container to evaluate subdirectories. Drag over the File Properties Task onto the pane and configure it to point to the file name that's presently being enumerated through in the For Each Loop Container. Next, configure the File Creation Date (or another date property) to output to an SSIS variable like FileCreationDate. Next, create a File System Task to remove the file using the Delete File operation. The last step is to create a precedence constraint between the two tasks and double-click on the constraint. Set the Evaluation Operation to Expression and Constraint and also set the Expression property to the following expression:

 DATEDIFF( "D", @FileCreationDate, getdate())  > 30

The final product package would resemble this screenshot.

File In Use

In large data loads where a file is your source, you want to ensure that you don't load a file that's still being transmitted to you. If you try to load a file that's still being FTPed or copied to you, you will receive a potential sharing violation and the package will fail. With the File Properties Task, you can look at the file you're about to upload to ensure it's not in use. If it is, skip that file in the processing chain and retry later.

To accomplish this inside a loop, drag over the File Properties Task onto the SSIS design pane and point it to the connection manager you're about to load. Next, output the File In Use property to a SSIS variable like FileInUse. Connect the task to whichever task will be loading the file. Then, double-click on the precedence constraint and set the Evaluation Operation to Expression and Constraint and also set the Expression property to @FileInUse == False. This wil ensure that the next task will only execute if the file is not in use and the final product will resemble a package that looks like the below.

  

Pricing

The File Properties Task is priced per machine license. The price of the task is:

  • Free in an *as is* license
 

Copyright ® 2008 Pragmatic Works