How can I use System::ErrorDescription system variable in the expression builder

I'm using BIML to create SSIS packages, and one section of a package deals with the errors occurred. I'm using an Execute SQL Task which calls a stored procedure to log the error details in the database, and the stored procedure expects a parameter @Comments which holds information of the error description and some other custom info.

I'm trying to create a variable Error_Details and use an expression to build the value for @Comments param.

<Parameters>
    <Parameter Name="ParentPkgID" DataType="Int32">0</Parameter>
</Parameters>

<Variables>
    <Variable Name="TableName" DataType="String">
        tblEmployee
    </Variable>
    <Variable Name="Error_Details" DataType="String" EvaluateAsExpression="true"> 
        &quot;Package execution failed, Error details :: &quot; + @[System::ErrorDescription]
    </Variable>
</Variables>

The problem is the package creation fails when I use the above code to create the variable, I understand System::ErrorDescription is not available in the expression builder, but what are my options here.

How can I use some system variables which are only available on some specific context in BIML code.

Thanks much for looking at my question. Please suggest a solution.

Solution

This is due to a limitation in SSIS, and the SSDT designer will give you a similar error if you try to use the above expression there.

SSIS only makes the System::ErrorDescription and System::ErrorCode variables available in the context of certain event handler types, such as OnError.

You could rearrange your package to create an event handler on the ExecuteSQL task, obtain the error description therein, and then process it using your stored procedure call from within the event handler. Here is some minimal sample code that will build as expected:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Package1">
            <Tasks>
                <Dataflow Name="DataflowTask1">
                    <Events>
                        <Event Name="OnError" EventType="OnError">
                            <Variables>
                                <Variable Name="Error_Details" DataType="String" EvaluateAsExpression="true">&quot;Package execution failed, Error details :: &quot; + @[System::ErrorDescription]    </Variable>
                            </Variables>
                        </Event>
                    </Events>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>