Sample RSExecutionLog Error

I have been using the reportserver function of sql 2005 for some time now and I decided to try the rsexecutionlog sample. I went through the installation and everything seemed to go fine until I executed the job. The job failed so I manually ran the dts package with configuration file and found that the error is from the parameters column query and reads like this:

"[Derived Column [979]] Error: The "component "Derived Column" (979)"

failed because truncation occurred, and the truncation row disposition

on "output column "ParametersStr" (999)" specifies failure on

truncation. A truncation error occurred on the specified object of the

specified component. "

"Task Update Parameters failed"

I read the query and it reads as such:

SELECT ExecutionLogID, Parameters

FROM ExecutionLogs WITH (NOLOCK)

WHERE Parameters IS NOT NULL AND

Datalength(Parameters) > 0

I checked both the source and destination columns and they have the same character type settings of ntext and same lengths. So I don't understand why it thinks there is a truncation occurring. Please help.

I ran into the same problem. The "Update Parameters" data flow in the SSIS package has a script step that reads the parameters and parses them into name/value pairs. The script task variable structure can't handle the entire amount of data being passed so it is throwing an error. I got around this by changing the "derived column" step just before the "script component" step in the "update parameters" data flow. In there, the variable ParametersStr is defined as "(DT_WSTR,4000)Parameters". I changed it to "(DT_WSTR,2000)Parameters". I also set the error output to ignore truncation errors. This may not be the best way to resolve this, but it worked for me and it only took a second.|||

This worked for me, thanks for sharing. I just saved the old dstx in case this were to cause any problems in the future.