Biml - Expression Task

The Expression Task, introduced with the 2012 release of SSIS, allows for the assignment of a value to an SSIS Variable. Prior to the 2012 release, you either needed to use a trivial, but custom component or perform this in a Script Task.

You'll find this Expression Task is a bit of a pain to work with. A classic expression for building a string of "YYYY-MM-DD" is (DT_WSTR, 4) YEAR(@[System::StartTime]) + "-" + RIGHT("0" + (DT_WSTR, 2) MONTH(@[System::StartTime]), 2) + "-" + RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2) Since that expression has double quotes in it and we'll need to be storing that string inside a string, we need to encode the expression's double quotes with their entity name "

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_Expression">
            <Variables>
                <Variable Name="DateString" DataType="String"></Variable>
                <Variable Name="Math" DataType="Int32">0</Variable>
                <Variable Name="Reference" DataType="String" EvaluateAsExpression="true">(DT_WSTR, 4)  YEAR(@[System::StartTime]) + "-" + RIGHT("0" + (DT_WSTR, 2)  MONTH(@[System::StartTime]), 2) + "-" + RIGHT("0" + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)</Variable>
            </Variables>
            <Tasks>
                <Expression
                    Name="EXP Add"
                    Expression="@[User::Math]=1+4"
                    >
                </Expression>
                <Expression
                    Name="EXP Assign file name"
                    Expression="@[User::DateString] = (DT_WSTR, 4)  YEAR(@[System::StartTime]) + &quot;-&quot; + RIGHT(&quot;0&quot; + (DT_WSTR, 2)  MONTH(@[System::StartTime]), 2) + &quot;-&quot; + RIGHT(&quot;0&quot; + (DT_WSTR, 2) DAY(@[System::StartTime]), 2)">
                </Expression>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

We've generated an SSIS package with two Expression Tasks. One performs simple math, the other assigns a date stamp to a string. Neither of these are particularly compelling reasons to use the Expression Task but I want you to observe the mechanics behind the creation of the tasks.

EXP Add

Here we're performing addition and assigning the result to @[User::Math]

EXP Assign file name

In this task, we're computing a date string. You'll see the double quote entity is correctly represented.