A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, February 25, 2014

Biml - File System Task

Biml - File System Task

The File System Task allows you to manipulate objects within the file system without resorting to a Script Task or the Execute Process Task.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_FileSystem">
            <Variables>
                <Variable Name="FileName" DataType="String">system.ini</Variable>
                <Variable Name="FolderBase" DataType="String">J:\SSISData</Variable>
                <Variable Name="FocusArea" DataType="String">Sales</Variable>
                <Variable Name="FolderInput" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Input"</Variable>
                <Variable Name="FolderArchive" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Archive"</Variable>
                <Variable Name="FolderOutput" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Output"</Variable>
                <Variable Name="FileSource" DataType="String" EvaluateAsExpression="true">"J:\\Windows" + "\\" + @[User::FileName]</Variable>
                <Variable Name="FileDestination" DataType="String" EvaluateAsExpression="true">@[User::FolderArchive] + "\\" + @[User::FileName]</Variable>
                <Variable Name="FileRenamed" DataType="String" EvaluateAsExpression="true">@[User::FolderArchive] + "\\MaximumOutput.txt"</Variable>
                <Variable Name="OverwriteDestinationFile" DataType="Boolean">True</Variable>
            </Variables>

            <Tasks>
                <FileSystem 
                    Operation="CreateDirectory" 
                    Name="FS CreateDirectory">
                    <VariableInput VariableName="User.FolderArchive"/>
                    <Annotations>
                        <Annotation AnnotationType="Description">Create a folder based on our Variable</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem 
                    Operation="CopyFile" 
                    Name="FS CopyFile">
                    <VariableInput VariableName="User.FileSource" />
                    <VariableOutput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Copy a file from source to destination</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem 
                    Operation="RenameFile" 
                    Name="FS RenameFile">
                    <VariableInput VariableName="User.FileDestination"/>
                    <VariableOutput VariableName="User.FileRenamed"/>
                    <Annotations>
                        <Annotation AnnotationType="Description">Rename the copied file to something else</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem
                    Operation="DeleteDirectoryContent"
                    DelayValidation="true"
                    Name="FS DeleteDirectoryContent">
                    <VariableInput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Delete everything in the folder</Annotation>
                    </Annotations>
                </FileSystem>
                
                <FileSystem
                    Operation="DeleteDirectory"
                    Name="FS DeleteDirectory">
                    <VariableInput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Remove the folder we just created</Annotation>
                    </Annotations>
                </FileSystem>
                    
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

That mess of biml creates 5 file system tasks. We create a set of folders \SSISData\Sales\Archive. Into the Archive folder, we will copy the system.ini file from the Windows folder. The next task renames it to MaximumOutput only to have all the contents of the Archive folder deleted prior to completely deleting the folder.

FS CreateDirectory

Create a directory, if it does not exist based on our Variable @[User::FolderArchive]

FS CopyFile

Using variables, we will copy our file, defined as @[User::FileSource] to our @[User::FolderArchive]

FS RenameFile

Rename our file from @[User::FileDestination] to @[User::FileRenamed]

FS DeleteDirectoryContent

Purge all the files in @[User::FolderArchive]

FS DeleteDirectory

Finally we drop the folder @[User::FolderArchive]

1 comment:

Vrunda said...

What is the attribute i need to use in biml for "UseDirectoryIfExist" attribute of SSIS FileSystem Task?