A truncation occurred during evaluation of the expression
First of all, I get the following error message for one of my packages which uses user variables: SSIS package "UsageAnalysis.dtsx" starting.Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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.SSIS package "UsageAnalysis.dtsx" finished: Failure. Now my package has the following variables: GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above GetAnalysisData: scope package level, type string, Evaluate as Expression "SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @[User::MaxUsageID] The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows "SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000" the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated. Thanks, Asaf
August 22nd, 2007 1:27pm

Are you sureMAX(UsageID)is1,000,000 and not 100,000,000? Try making your cast a longer length in the GetAnalysisData variable.Perhaps:(DT_STR,20,1252)@[User::MaxUsageID]
Free Windows Admin Tool Kit Click here and download it now
August 22nd, 2007 3:58pm

Hi Phil, Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable. select max(usageID) from dbo.XXX --1027005 So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @[User::MaxUsageID] Even when I have no casting involved such as "SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000" I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here. Thanks, Asaf
August 22nd, 2007 4:59pm

Phil, I have worked it out. It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252)to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it. When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I haddone everything right. To resolve that, I just decided to define a new variable with required DT_STRstring length and my package works without any complain. Thanks, Asaf
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2007 2:54pm

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression. With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it. Thanks, Phil
August 23rd, 2007 3:30pm

I also had truncation error problem when executing a query which includes (DT_STR , 6, 1252) @[User::RecordsInserted] . It is noticed that the no. of records inserted is more than 6 characters in length and when I changed that to (DT_STR , 10, 1252) @[User::RecordsInserted] , it is working fine without any errors.Suman
Free Windows Admin Tool Kit Click here and download it now
December 30th, 2010 7:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics