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

Find ramblings

Monday, March 3, 2014

Biml - FTP Task

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>

Result

A package is created with an FTP connection manager, expressions applied to it, and 3 FTP Tasks.

FTP Receive File

A task is created to receive, GET, a file. This operation type is set directly in the Biml as a Send but we later override it via Expresions.

Expressions

We're going to set the operation type by the numeric value of the Operation property.

FTP Delete downloaded file

An FTP task set to Delete Local Files

FTP Receive Expression on OperationName

This step is identical to the first except in our application of Expressions.

Expressions

You'll observe the Operation remains "Send files" despite setting OperationName to either the value of "Receive" or "Receive files"

No comments: