May 24, 2011 at 4:25 pm
Using SQL Server 2008. I am new to SSIS. Trying to create my first SSIS package. I added XML source, DataConversion and SQL server Destination objects to me design. Assigned the source XML and XSD to the XML source. Mapped the source element from XML to destination datatypes in the data conversion object, then mapped the converted columns to destination table on sql server. When I run this package I get the following errors:
"
[Data Conversion [3216]] Error: Data conversion failed while converting column "MSH.10" (49) to column "Copy of MSH.10" (3225). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [3216]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of MSH.10" (3225)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of MSH.10" (3225)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (3216) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3217). 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.
"
The value in MSH10 in the XML file is 9548001 and I have set it to convert it to 4 byte signed integer (DT-14) to go in Copy of MSH.10, which is then mapped to a PK int not null column in my table.
Please help me understand what am I doing wrong.
Thank you.
May 24, 2011 at 4:28 pm
It's truncating, basically. The value coming in from the XML is bigger then a 4 byte int. Try an 8 byte BIGINT.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 24, 2011 at 4:33 pm
If I use 8 bit signed for the conversion, I get error: conversion between DT_18 and DT_14 is not supported.
May 24, 2011 at 4:57 pm
You have to change the originating definition in the XML file connection, so the metadata is modified.
Basically you'll have to start at the beginning of the flow and track it through from end to end, and you may need to close and re-open BIDS/VS2k8 to get it to refresh the metadata on the target table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 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