October 26, 2010

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED

Recently, while working on a db integration using SSIS, I had encountered a generic error while working with an XML Datasource: Error Code DTS_E_PRIMEOUTPUTFAILED. There are several scenarios that can produce this error, and there also was another message specifying that the offending element was at character 14988 in the XML. The data at that point in the file looked fairly straightforward, which then led me down the path to thinking the file size might be the problem:

<word>with</word>
<word>a</word>
<word>vegetable</word> <- supposedly offending line
<word>white</word>
<word>rice</word>

After whittling down the data for a test, the problem still existed, so the problem wasn't file size either. It ended up just up being bad data... there were several ampersand (&) characters throughout the file.
The error message referencing character 14988 was just a confusing diversion. After scrubbing the data, SSIS was then able to successfully parse the file and use it as a datasource.

Here's a quick reference to characters needing to be escaped within XML, and their associated escape sequences:

Ampersand(&) - &amp;

Greater Than symbol (>) - &gt;

Less Than symbol (>) - &lt;

Double Quote (") - &quot;

Single Quote (') - &apos;

Share |