December 11, 2009 at 1:02 pm
I have searched and searched....my google fu has failed me.
I am trying to export a table to a flat file. The table contains a column that is of type XML.
I have tried to do a Data Conversion task but it fails. My task is converting the XML Data Type Column (DT_NTEXT) and converting it to DT_TEXT to export to my ANSI flat file.
Here is the error I get:
Error: 0xC02020C5 at Workout_CBR_Error_Response, Data Conversion [1303]: Data conversion failed while converting column "ErrorResponse" (64) to column "ErrorResponse" (1403). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Workout_CBR_Error_Response, Data Conversion [1303]: The "output column "ErrorResponse" (1403)" failed because truncation occurred, and the truncation row disposition on "output column "ErrorResponse" (1403)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047022 at Workout_CBR_Error_Response: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (1303) failed with error code 0xC020902A. 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.
Error: 0xC0047021 at Workout_CBR_Error_Response: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC020902A. There may be error messages posted before this with more information on why the thread has exited.
I just cant figure out why it is failing. I would think I could export the XML (actually a Soap Envelope) to text without issue.
December 11, 2009 at 2:20 pm
December 12, 2009 at 5:24 am
John Rowan (12/11/2009)
What data source are you using to the the XML column into the pipeline?
the source is an OLEDB connection. I am not in front of it now, but I think there are 3 Varchar, 2 int and 1 XML column. The export works fine if I don't export the XML column, but once I include that, I get errors with our without the conversion. Without the conversion, it says that the DT_NTEXT is not supported in ANSI and if I throw the conversion in there the conversion fails with the errors posted in my original post.
December 14, 2009 at 12:30 pm
Huh, sounds wierd. You should be able to use the data conversion transformation for this. I'm not saying your wrong and I believe you are getting erros, I'm just saying that it 'should' work.
Here's a workaround that you can try. Since you are using an OLE DB source connection, you should be able to configure the source to be a query. In your source query, CAST the XML column to varchar(max).
SELECT varchar1,
varchar2,
varchar3,
int1,
int2,
CAST(xmlcol as varchar(max)) as xmlcol
FROM YourTable
This should get the XML into the pipeline as text data and your should be able to work with it.
December 15, 2009 at 6:49 am
After you change the data type using the script and if you're still getting the error try to change the error output of the column to ignore truncation errors.
December 15, 2009 at 7:32 am
John Rowan (12/14/2009)
Huh, sounds wierd. You should be able to use the data conversion transformation for this. I'm not saying your wrong and I believe you are getting erros, I'm just saying that it 'should' work.Here's a workaround that you can try. Since you are using an OLE DB source connection, you should be able to configure the source to be a query. In your source query, CAST the XML column to varchar(max).
SELECT varchar1,
varchar2,
varchar3,
int1,
int2,
CAST(xmlcol as varchar(max)) as xmlcol
FROM YourTableThis should get the XML into the pipeline as text data and your should be able to work with it.
Whoo Hooo!! that worked. Sometimes the simplest solutions get overlooked. Now I just need to get the CR\LF removed from the XML and I will be set.
December 15, 2009 at 9:31 am
You can encorporate that into your SELECT as well.
SELECT varchar1,
varchar2,
varchar3,
int1,
int2,
REPLACE(CAST(xmlcol as varchar(max)), CHAR(13) + CHAR(10), '') as xmlcol
FROM YourTable
January 28, 2010 at 10:24 am
You mentioned that you have a "Soap Envelope" in your table,
is that on a typed xml?
I am trying to create a XML SCHEMA COLLECTION for a soap message, but it does not let me,
were you able to do that?
the following is an example of what I am trying to do. can I do that?
I have sql server 2008.
thank you.
Nabila
declare
@xmlStr xml
set @xmlStr = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<AuthenticationHeader xmlns="http://tempuri.org/">
<Username>string</Username>
<Password>string</Password>
<Domain>string</Domain>
</AuthenticationHeader>
<RequestInfoParameters xmlns="http://tempuri.org/">
<ContentLanguage>int</ContentLanguage>
</RequestInfoParameters>
</soap:Header>
<soap:Body>
<DeleteUserByID xmlns="http://tempuri.org/">
<UserId>long</UserId>
</DeleteUserByID>
</soap:Body>
</soap:Envelope>'
CREATE XML SCHEMA COLLECTION Ekt_UserCreate AS @xmlStr
and I am getting
Msg 2378, Level 16, State 1, Line 23
Expected XML schema document
February 1, 2010 at 9:54 am
Sorry, I can't help with getting the data into the table. It is put there by a process that I don't control.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy