Conversion failed when converting the varchar value to data type int in SQL Server
The first thing I would do is identify rows where those 6 characters can't possibly be an integer.

SELECT ProcessInstanceAppianID, ProcessInstanceDescription
  FROM InternalUseOnly.dbo.processinstance
  WHERE RIGHT(ProcessInstanceDescription, 6) 
    NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';


Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.

In SQL Server 2012+ you can use TRY_CONVERT() instead.