SSIS Dimension Processing Error: XML parsing failed at line X, column Y: Illegal xml character.

01 Jun
June 1, 2012

Without a doubt one of the most annoying and least useful errors I have ever encountered during my experience with SSIS.

This error has crept up on an SSIS packages that processes a dimension. The SSIS package is very simple and all it has is a OLE DB Source that dumps data from a query into a Dimension Processing task. The package executes fine until it reaches a particular row in the OLE DB source batch, in which it will fail with the following error message:

Dimension Processing Error: XML parsing failed at line 4, column 3391: Illegal xml character.

Descriptive!

The full error message looks like this:

Dimension Processing Error: XML parsing failed at line 4, column 3391: Illegal xml character.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Dimension Processing” (2) failed with error code 0x80004005 while processing input “AnalysisServicesServerInput” (15). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[Dimension Processing [2]] Error: XML for Analysis parser: The commit or rollback request cannot be completed because there is no active transaction in this session.

[SSIS.Pipeline] Error: Dimension Processing failed the post-execute phase and returned error code 0x80004005.

To give you abit of a background on the issue, the dimension is a simple one, and has the following attributes:

  • Keyword: using a “NameColumn” of type string and of size NVARCHAR(125)). the “KeyColumns” is an INT (KeywordID).
  • IsBrand: using a “KeyColumns” of type boolean.

I am trying to process the dimension in FULL mode, although the error is not specific to the processing mode (as I switched it to ADD or UPDATE with the same issue).

The hint to the solution is clearly in the first error message. What SSIS’ Dimension Processing Task actually does is package the data received from the OLE DB stream as an XML file before sending it off to be consumed by the cube database, this will be a batched stream obviously (otherwise the XML will be too large).

In order to zero-in on the issue, I reduced the dimension to simply an ID attribute (KeywordID), the dimension processed fine, so I deduced it was an issue in the NameColumn binding (which is bound to an NVARCHAR(125) string column). After some test I could prove that the issue stemmed from a particular keyword, but which one!, SSIS cannot tell what caused the error, as this is the error message received from the SSAS server, which has already rolled back the transaction and so all previously inserted data, so there is no way of detecting the incriminating keyword using standard error output handling mechanism techniques in SSSIS

After a lot of time spent trying to limit the dimension input row set, in order to zoom in on the keyword, I found the issue was in a character that was not supported by the standard XML encoding format. After removing this character (and any character not supported by the XML encoding) using SSIS Script-Component Transformation, I was able to resolve the issue.

For a script that removes all non-UTF-8 characters from a string refer to SO.

This has cost me dear hours, so I hope it saves you some!

SSIS Dimension Processing Error: XML parsing failed at line X, column Y: Illegal xml character. rated 4 out of 5 by 1 readers

SSIS Dimension Processing Error: XML parsing failed at line X, column Y: Illegal xml character. , 4.0 out of 5 based on 1 ratings

* * * *   1 vote
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>