July 3, 2018 at 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
July 3, 2018 at 7:30 am
lindsayscott23 - Tuesday, July 3, 2018 7:03 AMHi,
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.
July 3, 2018 at 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!
July 3, 2018 at 11:23 am
lindsayscott23 - Tuesday, July 3, 2018 9:37 AMI 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.
July 4, 2018 at 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:
What confuses me is that there isn't any error code thrown, just that there was an "error"...
Cheers,
lins
July 4, 2018 at 5:54 am
lindsayscott23 - Wednesday, July 4, 2018 3:03 AMHi 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:
- Error: The expression for variable "FileLocation" failed evaluation. There was an error in the expression.
- 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.
July 4, 2018 at 6:31 am
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
July 4, 2018 at 6:37 am
lindsayscott23 - Wednesday, July 4, 2018 6:31 AMI 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.
July 4, 2018 at 6:57 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply