Simplifying File Operation in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

In previous version of SSIS (i.e.: DTS), if you want to do file or directory related operation, you have to write this in ActiveX script task. For one or two operation, it would be no problem to maintain, but imagine when you ETL process involves many file or directory operation, you will need some efforts to understand and modify the ActiveX scripts. So it is not practical anymore in this situation.

SSIS provide File System Task component to simplify these processes by just selecting appropriate already built-in functions inside the task, such as Copy Directory, Copy File, Create Directory, Delete Directory, Delete Directory Content, Delete File, Move Directory, Rename File, and Set Attributes.

Let’s say if you want to Move Directory along with their files inside, some properties that should be specified below, based on category : Source Connection and Destination Connection.

For Source Connection property group, there are 2 properties, if you would like to assign value from variable to SourceConnection property, set it true on IsSourcePathVariable, then you can select drop down values on SourceVariable property.

For Destination Connection property group, there are 3 properties, similar to Source Connection property group, there are properties IsDestinationPathVariable and DestinationConnection. If you want to override existing file or directory and destination when file/directory name similar with source, you can set the value to true for Overwrite Destination property.

With complete function of the task, you will remove necessary to write complex ActiveX script and the package becomes easy to maintain.