SSIS; Why would the "The expression for variable "FileLocation" failed evaluation." error appear, but the package succeed?

  • Hi,

    Hope you can help!

    I have a .dtsx package that drops a flat file off in a folder. I'm using a variable (run as expression) so the package can dynamically suffix the file name with today's date.

    The package runs successfully but throws a "The expression for variable "FileLocation" failed evaluation." error. No other error messages appear.

    I've checked the case sensitivity between the variable and the expression wherever it appears and it seems fine.

    Any idea why this happens and how I can fix it?

    Cheers

    lins

  • lindsayscott23 - Tuesday, July 3, 2018 7:03 AM

    Hi,

    Hope you can help!

    I have a .dtsx package that drops a flat file off in a folder. I'm using a variable (run as expression) so the package can dynamically suffix the file name with today's date.

    The package runs successfully but throws a "The expression for variable "FileLocation" failed evaluation." error. No other error messages appear.

    I've checked the case sensitivity between the variable and the expression wherever it appears and it seems fine.

    Any idea why this happens and how I can fix it?

    Cheers

    lins

    Does the error appear at design time, or run time?
    Packages cannot simultaneously run successfully and throw an error, as far as I know.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I think it's appearing at the run time. When I execute the package all the validation steps succeed, and the flat file appears in the right folder location, with the right date suffix.
    The olde favourite error appears:

    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 
    The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1);
    resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount.
    Change the MaximumErrorCount or fix the errors.

    Usually when I see this the package itself fails, but in this case it "completes with error".
    I'm considering altering the MaximumErrorCount, but that feels too much like cheating!

  • lindsayscott23 - Tuesday, July 3, 2018 9:37 AM

    I think it's appearing at the run time. When I execute the package all the validation steps succeed, and the flat file appears in the right folder location, with the right date suffix.
    The olde favourite error appears:

    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 
    The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1);
    resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount.
    Change the MaximumErrorCount or fix the errors.

    Usually when I see this the package itself fails, but in this case it "completes with error".
    I'm considering altering the MaximumErrorCount, but that feels too much like cheating!

    Can you include the text of the two error messages as well?
    I agree that adjusting MaxErrorCount is cheating and should be avoided in 99.9% of all scenarios, in my opinion.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil,

    I've just run the package and again it's worked fine (delivered the flat file as it should) and once again thrown the error messages.

    There's only two messages. Here they are:

    1. Error: The expression for variable "FileLocation" failed evaluation. There was an error in the expression.
    2. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    What confuses me is that there isn't any error code thrown, just that there was an "error"...

    Cheers,

    lins

  • lindsayscott23 - Wednesday, July 4, 2018 3:03 AM

    Hi Phil,

    I've just run the package and again it's worked fine (delivered the flat file as it should) and once again thrown the error messages.

    There's only two messages. Here they are:

    1. Error: The expression for variable "FileLocation" failed evaluation. There was an error in the expression.
    2. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    What confuses me is that there isn't any error code thrown, just that there was an "error"...

    Cheers,

    lins

    Can you paste the expression you are using for FileLocation?

    Another thing to do (if you have the package deployed to SSISDB) is to check the 'All Executions' report, in an attempt to determine at exactly which point the error is thrown.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi,

    I deployed the package in SSISDB, the All Executions report gives more detail, but no other information about the error (other than it gets thrown at the very end of the package execution).

    The expression is created by running a string variable as an expression. Here's how it 'looks' in SSIS:

    Name = FileLocation
    Scope = ProjectName
    DataType = String
    Value = \\server\folder\subfolder\
    Expression = @[User::FileLocation]
    Evaluate as expression = True

    I just ran it passed a colleague who thought the error might be caused by the circular nature of creating a variable which uses itself as the expression. Might that be it?

    Cheers,

    Lins

  • lindsayscott23 - Wednesday, July 4, 2018 6:31 AM

    I just ran it passed a colleague who thought the error might be caused by the circular nature of creating a variable which uses itself as the expression. Might that be it?

    Cheers,

    Lins

    Yes. That expression does nothing of value, I suggest you remove it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi,

    That's brilliant, I removed it and not only did the error message disappear, but the package ran in about a quarter of the time.
    Adding the variable as an expression was something that I think I picked up from an SSIS blog years ago. I just took the advice t face value and never really questioned it.  Lesson learnt anyway.

    Cheers loads for your help with this one Phil,

    Lins

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply