Using The File System Task in SQL Server 2008 Integration Services


SQL Server
Integration Services (SSIS) is a Business Intelligence tool which can be used
by database developers or administrators to perform extract transform &
load (ETL) operations. In my previous article entitled Container Tasks in SQL Server 2008
Integration Services

I discussed the different types of container tasks such as For Loop Container,
Foreach Loop Container, Task Host and Sequence Container and how they can be
used by database developers. In this article I will examine the use of the File
System Task which is available in SQL Server 2005 and later versions of Integration
Services.

Overview
of Data Preparation Tasks in SSIS 2005 & Later Versions

The data
preparation tasks within SSIS can be used to retrieve data or to validate the
quality of data. The different types of data preparation tasks which are
available in SSIS are the File System Task, FTP Task, Web Service Task, XML
Task and Data Profiling Task.

Overview
of the File System Task

The File
System Task can be used to perform different types of operations on files and
directories such as create, move, delete, and also to set the attributes for
files and folders. The different types of operations which can be performed by the
File System tasks are shown in the below snippet.

· Copy Directory
can be used to copy all the files from one directory to another. You will need
to configure both the source and destination directories when using this
operation of the File System task.

·
Copy File can
be used to copy a file from one location to another location. You will need to
configure both the source and destination when using this operation of the File
System task.

·
Create
Directory can be used to create a folder. You will need to specify the source
location where the folder needs to be created.

·
Delete
Directory can be used to delete a folder from a specified location. You will need
to specify the source location from where the folder needs to be dropped.

·
Delete
Directory Content can be used to delete all the files available within the user
specified directory.

·
Delete File can
be used to delete a user specified file from the specified folder.

·
Move Directory
can be used to move a folder/directory from one location to another location.

·
Move File can
be used to move a file from one location to another.

·
Rename File can
be used to rename a file available within a specified location.

·
Set Attributes can
be used to set attributes to files and folders. Different types of attributes
which can be set are hidden, archive, read only and system.

One
important point to note is that the File System Task can operate on a single
file or a directory at a time, so if you want to perform an operation on
multiple tasks you will need to run this task within a Foreach Loop container.

Example
– File System Task

We will
run through an example using the File System task to copy MDF and LDF files of the
Resource Database from the “C:DDBackupsSystemDatabaseBackups” folder to the“C:DBBackupsSystemDatabaseBackupsResourceDB”
folder. Once the files are copied we will be setting the attributes of ResourceDB
folder to ReadOnly. For more info on the Resource database which is available
in SQL Server 2005 and later version you can refer to my previous article entitled
Importance
of the Resource Database

1.      
Create a new
SQL Server Integration Services Project and rename the default package to FileSystemTaskExample.dtsx

2.      
Double click FileSystemTaskExample.dtsx
package to open it up in Design mode.

3.      
Add variables
named ResourceDB_MDF_File, ResourceDB_LDF_File and DestinationDirectory, you
can open up the Variables window by right clicking within the control flow
design window and by choosing the Variables option from the drop down list as
shown in the below snippet.

4.      
Once the   Variables
window opens up, click on Add Variable button and provide the details as shown
in the below snippet.

5.      
Drag and drop “File
System Task” from the toolbox to the Control Flow window and rename it “Copy
ResourceDB MDF File
”. Then double click the File System Task to open up
the editor window as shown in the below snippet.

The
File System Task Editor should be configured with the details shown in the
below table.

Destination Connection

IsDestinationPathVariable

True

DestinationVariable

User::DestinationDirectory

(Choose the user variable from the Drop-down list)

OverwriteDestination

True

General

Name

Copy ResourceDB MDF File

Description

File System Task

Operation

Operation

Copy file (Choose this option from the Drop-down list)

Source Connection

IsSourcePathVariable

True

SourceVariable

User::ResourceDB_MDF_File

(Choose the user variable from the Drop-down list)

Continues…

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |