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
|