Biml - FTP Task

SSIS has some really awesome tasks to improve your ability to acquire, manage and push data about. The FTP Task isn't one of them.

I cut my teeth with FTP on a command line so the limited options available via the native task are rather bothersome. Compound that with some of the other annoyances, like erroring when a file does not exist, you can see why I prefer to use a command file.

In this example, we are going to access a publicly available ftp server using anonymous user and blank password. We'll download a file into our working folder and then delete it.

As you might have observed throughout my postings, I really believe the power of SSIS comes through using Expressions. Something that surprised me was that as of this writing, I cannot use an Expression on the OperationName property of an FTP task. Well, I can apply one, but I have no idea what the text string that should go inside of one of those. Instead, by editing an existing package's operationg via Visual Studio and then viewing the resulting XML, I was able to discern the correlation between the displayed text and the underlying Operation numeric value.

0: Send file
1: Receive file
2: Delete Local Files
3: Delete Remote Files
4: Create Local Directory
5: Create Remote Directory
6: Remove Local Directory
7: Remove Remote Directory

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FtpConnection Name="CM_FTP" ServerName="localhost" UserName="user" Password="pass" ServerPort="21" UsePassiveMode="true" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_FTP">
            <Connections>
                <Connection ConnectionName="CM_FTP">
                    <Expressions>
                        <Expression PropertyName="CM_FTP.ServerName">@[User::ServerName]</Expression>
                        <Expression PropertyName="CM_FTP.ServerPort">@[User::ServerPort]</Expression>
                        <Expression PropertyName="CM_FTP.ServerUserName">@[User::ServerUserName]</Expression>
                        <!--<Expression PropertyName="CM_FTP.ServerPassword">@[User::ServerPassword]</Expression>-->
                        <Expression PropertyName="CM_FTP.Timeout">@[User::Timeout]</Expression>
                        <Expression PropertyName="CM_FTP.UsePassiveMode">@[User::UsePassiveMode]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Variables>
                <Variable Name="ServerName" DataType="String">ftp2.census.gov</Variable>
                <Variable Name="ServerPort" DataType="Int32">21</Variable>
                <Variable Name="ServerUserName" DataType="String">anonymous</Variable>
                <Variable Name="ServerPassword" DataType="String">
                    <Annotations>
                        <Annotation AnnotationType="Description">Don't ever do this</Annotation>
                    </Annotations>
                </Variable>
                <Variable Name="Timeout" DataType="Int32">60</Variable>
                <Variable Name="UsePassiveMode" DataType="Boolean">False</Variable>
                <Variable Name="LocalFolder" DataType="String">C:\ssisdata</Variable>
                <Variable Name="LocalFile" DataType="String">p3p.xml</Variable>
                <Variable Name="RemoteFolder" DataType="String">/w3c</Variable>
                <Variable Name="RemoteFile" DataType="String">p3p.xml</Variable>
                <!-- 
                0 Send file 
                1 Receive file
                2 Delete Local Files
                3 Delete Remote Files
                4 Create Local Directory
                5 Create Remote Directory
                6 Remove Local Directory
                7 Remove Remote Directory
                -->
                <Variable Name="Operation" DataType="Int32">1</Variable>
                <!--
                The text I have been using does not seem to align with values
                -->
                <Variable Name="OperationName" DataType="String">Receive</Variable>
                <Variable Name="OperationName2" DataType="String">Receive files</Variable>
                <Variable Name="RemotePath" DataType="String" EvaluateAsExpression="true">@[User::RemoteFolder] + "/" + @[User::RemoteFile]</Variable>
                <Variable Name="LocalPath" DataType="String" EvaluateAsExpression="true">@[User::LocalFolder] + "\\" + @[User::LocalFile]</Variable>
                <Variable Name="IsTransferTypeASCII" DataType="Boolean">True</Variable>
                <Variable Name="OverwriteDestination" DataType="Boolean">True</Variable>
            </Variables>
            <Tasks>
                <Ftp 
                    ConnectionName="CM_FTP" 
                    Operation="Send" 
                    Name="FTP Receive File"
                    IsTransferTypeAscii="false"
                    OverwriteDestination="true"
                    >
                    <VariableInput VariableName="User.LocalFolder"></VariableInput>
                    <VariableRemotePath VariableName="User.RemotePath" />
                    <Expressions>
                        <!-- I must not be using the right text here-->
                        <!--<Expression PropertyName="OperationName">@[User::OperationName]</Expression>-->
                        <Expression PropertyName="Operation">@[User::Operation]</Expression>
                        <Expression PropertyName="IsTransferTypeASCII">@[User::IsTransferTypeASCII]</Expression>
                        <Expression PropertyName="OverwriteDestination">@[User::OverwriteDestination]</Expression>
                    </Expressions>
                </Ftp>

                <Ftp 
                    ConnectionName="CM_FTP" 
                    Operation="Send" 
                    Name="FTP Delete downloaded file">
                    <VariableInput VariableName="User.LocalPath"></VariableInput>
                    <Expressions>
                        <Expression PropertyName="Operation">2</Expression>
                    </Expressions>
                </Ftp>

                <Ftp
                    ConnectionName="CM_FTP"
                    Operation="Send"
                    Name="FTP Receive Expression on OperationName"
                    IsTransferTypeAscii="false"
                    OverwriteDestination="true"
                    >
                    <VariableInput VariableName="User.LocalFolder"></VariableInput>
                    <VariableRemotePath VariableName="User.RemotePath" />
                    <Expressions>
                        <!-- I must not be using the right text here-->
                        <Expression PropertyName="OperationName">@[User::OperationName]</Expression>
                        <Expression PropertyName="IsTransferTypeASCII">@[User::IsTransferTypeASCII]</Expression>
                        <Expression PropertyName="OverwriteDestination">@[User::OverwriteDestination]</Expression>
                    </Expressions>
                </Ftp>                
            </Tasks>
        </Package>
    </Packages>
</Biml>