File Properties Task – (SQL Server Integration Service)

SQL Server Integration Service – File Properties Task

Hi. Let’s try to explore more Integration Services, again, and this time I am going to walk you through a custom task. The name of the task is “File Properties Task”. The download is free for the community and you can download the task executable from CodePlex.

Often many are of the opinion that the Script Task is the ideal place where one can compose a programming logic to suit the project need, of course this is just one option and as a matter of fact there are bundle of free and also paid executables available which can be downloaded and embedded while designing the package.

One such instance is presented in this post. The File Properties Task will help you with multitude of issues; some of them are listed below:

• Check for file availability
• Can be made to act as File System Watcher
• Get file relative name
• Get file absolute name
• Get file size
• Get file information about various file attributes like creation date, modified date, read only status and more.

Before we begin with some illustrative examples, let’s figure out how this task can be a part of the existing tool box set in Integration Services.

• First you need to download the task executable by clicking here
• Install the executable
• Open BI development studio and select Choose Items from the Tool box menu
• Now click on SSIS Control Flow Items and as you browse the menu you will find an entry listed for “File Properties Task”. Click on the checkbox and that’s all.

Let’s now start with few features:

In this window the designer can specify the file name, and desired action when file is not found

In this window, the designer can extract values like file name, creation time, file size and store them in user variables

And in this window the use extract few advanved propoerties such as read only status, hidden etc. and store them in user variables

Quick tips:

• The designer has the flexibility to pass a static or a variable source file name to the task
• The task can be configured to wait for a particular file, till the file is made available at the desired path
• The task can be configured to act as File System Watcher.

Let us now see some practical usage of this task

Check for file availability

The existing File System Task has no feature which will allow the program to verify whether the file is available at time of execution.  Nevertheless, the option available is:

• Code a Script Task and check for file existence

Steps to follow using the File Properties Task

• Create a Boolean type variable which would store the result of whether file found or not
• Specify the file name to be tested, either a static name or a name derived from a variable
• For test purpose, you can now connect this to a Script task and code for a message box to show the value returned by the variable.
• Or, one can come with a desired logic that the program should do in case the file is found or not found.

Get file attributes

This perhaps, is where the task reveals its beauty. Again since Integration Services offers no task which can be used to retrieve file attributes, the only method was to build a custom code using FileInfo class of .NET and store the necessary values in variables or any storage object (file, table etc.)

One may consider this task as a layer of encapsulation over the .NET FileInfo class.

Steps to follow using the File Properties Task

• Let us first create few user variables like sFileName, bFileReadOnly, sFileCreatedDate etc.
• With these variables, we can now configure the File Properties Task to store related values in them which of course can be used at a later stage during the program flow as shown in the graphic 2 above

That is all. Happy Learning!!

Regards

Raunak Jhawar

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook