• Marek Vavrovic

File System Task in SSIS

The File System task performs operations on files and directories in the file system. For example, by using the File System task, a package can create, move, or delete directories and files. You can also use the File System task to set attributes on files and directories.


Predefined File System Operations


Operation Description

Copy directory Copies a folder from one location to another.

Copy file Copies a file from one location to another.

Create directory Creates a folder in a specified location.

Delete directory Deletes a folder in a specified location.

Delete directory Deletes all files and folders in a folder.

content

Delete file Deletes a file in a specified location.

Move directory Moves a folder from one location to another.

Move file Moves a file from one location to another.

Rename file Renames a file in a specified location.

Set attributes Sets attributes on files and folders. (Archive, Hidden, Normal, ReadOnly,

System)


File System Task is located on Control Flow tab, section Common.


A: Create directory


1: Add file system task and open it up.

2: SourceConnection (New connection)

3: Usage type (Create folder)

4:Folder ( Specify the directory path and new directory name.) >>click OK


5: UseDirectoryIfExists (True) if the directory already exists a new one wont be created.

6: Click OK and press the start button to run the task.

SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)


B: Create directory <<CurrentDate>>using variable


Add file system task

  1. add variable TodaysDate, type String

  2. go to Expression builder

  3. SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)

  4. this code will generate the new directory name which is the current date.

5. add another variable holding the new directory location, in my case it will be I:\

Data type : String. You can just type the value in.

6. Concatenating these two variables we create another one which will be used for the file system task in the file connection manager: @[User::DirectoryPath]+ @[User::TodaysDate]

6.1 create variable

6.2 click on the dots to start the Expression Builder

6.3 connect these two variables @[User::DirectoryPath]+ @[User::TodaysDate] to get the finale variable.

7. IsSourcePathVariable = True

UseDirectoryIfExists = True

SourceVariable= User::ConManager



8. ConManager variable is empty. Set property Delay Validation to true.


9. run the task..

C: Create directory <<CurrentMonth>>using variable


This task create a new directory named after the current month.


1. add file system task

2. add variable DirectoryPath. To create a directory you need the directory path and directory name. I use another variable for directory name and the final step will be joining these two variables together.

3. We create a variable that will extract the month or its abbreviation from getdate() function.

3.1 variable MonthName, data type: String. Press F4 for properties. Set EvaluateAsExpression: True. Then go to Expression, click on the dots[...] to launch the Expression Builder.


4. Use this expression for the MonthName variable. Click OK.

@[User::MonthName] = MONTH(getdate()) == 1 ? "Jan" :

MONTH(getdate()) == 2 ? "Feb" :

MONTH(getdate()) == 3 ? "Mar" :

MONTH(getdate()) == 4 ? "Apr" :

MONTH(getdate()) == 5 ? "May" :

MONTH(getdate()) == 6 ? "Jun" :

MONTH(getdate()) == 7 ? "Jul" :

MONTH(getdate()) == 8 ? "Aug" :

MONTH(getdate()) == 9 ? "Sep" :

MONTH(getdate()) == 10 ? "Oct" :

MONTH(getdate()) == 11 ? "Nov" :"Dec"

5. We create a variable containing directory path and directory name joining the previous two variables together. This variable will be used for File system task.


6. Use ConManager variable for Create Directory operation in the File System Task.





13 views0 comments

Recent Posts

See All