Post reply

SSIS error

  • November 17, 2009 at 1:24 pm

    #137279

    Hi,

    I have an SSIS package that grabs data from an xml file and populates a table XYZ in sql 2005 .

    There is a field in XYZ called SubCatID which is int .

    When the SubCatID in the XML file is greater than 255 than it throws an error saying

    [XML Source [1]] Error: The value was too large to fit in the output column "SubCategoryId" (40).

    [XML Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "XML Source" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "SubCategoryId" (40)" 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.

    Below is a sample xml file

    <AD>

    <DescriptionTEXT</Description>

    <Location>Phoenix</Location>

    <DateCreated>20091117</DateCreated>

    <AdId>2502070</AdId>

    <SubCategory>Manufactured Homes for sale</SubCategory>

    <SubCategoryId>335</SubCategoryId>

    <MainCategory>REAL ESTATE FOR SALE</MainCategory>

    <MainCategoryId>300</MainCategoryId>

    <PremiumFlag>N</PremiumFlag>

    <AdType>INDIVIDUAL</AdType>

    <Emailaddress>garrison5861@hotmail.com</Emailaddress>

    </AD>

    Any help appreciated .

  • November 17, 2009 at 1:33 pm

    #1080515

    Right-click on your XML Source and select Show Advanced Editor.

    Go to the Input and Output Properties tab.

    Expand your output and it's Output Columns folder.

    Click on the SubCategoryID field and change it's Length property to a number larger than 255, or better yet, change it's Data Type to an integer.

  • November 17, 2009 at 1:57 pm

    #1080528

    I followed what you suggested . But the SubcatId in the Output columns shows a datatype of numeric[DT_NUMERIC] and the length is disabled . So I couldnot change to something greater than 255 .

  • November 17, 2009 at 2:00 pm

    #1080534

    But did you try changing the datatype?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 17, 2009 at 2:04 pm

    #1080536

    If you change it to either DT_STR or DT_UI4 you will likely be ok, for the DT_STR you will be able to set the length, for DT_UI4 you won't it is equivalent to a sql int.

    CEWII

  • November 17, 2009 at 2:26 pm

    #1080551

    I tried to change it to DT_UI4 . But it did not help either . I couldnot change it to DT_STR . "Invalid value" error shown .

  • November 17, 2009 at 2:41 pm

    #1080557

    What's the data type of the field in the XSD?

  • November 17, 2009 at 2:44 pm

    #1080559

    <xs:element minOccurs="0" name="SubCategoryId" type="xs:unsignedByte" />

  • November 17, 2009 at 2:44 pm

    #1080560

    Ok, for DT_NUMERIC length is greyed out but what are the values for precision and scale?

    Also, you might consider screen printing the advanced editor screen with that field selected and the type, length, precision and scale visible..

    CEWII

  • November 17, 2009 at 2:47 pm

    #1080561

    PSB (11/17/2009)

    <xs:element minOccurs="0" name="SubCategoryId" type="xs:unsignedByte" />

    Well that explains a lot. unsigned byte = tinyint, not sure what the valid value is for integer though..

    CEWII

  • November 17, 2009 at 2:49 pm

    #1080563

    Precision = 18 and Scale = 0

  • November 17, 2009 at 2:56 pm

    #1080567

    Precision = 18 and Scale = 0

  • November 17, 2009 at 3:13 pm

    #1080576

    Also the error : [XML Source [1]] Error: The value was too large to fit in the output column "SubCategoryId" (3198).

  • November 17, 2009 at 3:18 pm

    #1080578

    I think your XSD needs to change, the value in the file is not satisfying the type specified. If the value is really an integer then the XSD needs to reflect that. And then the output column does too..

    CEWII

  • November 17, 2009 at 3:27 pm

    #1080584

    Thanks I changed the XSD to <xs:element minOccurs="0" name="SubCategoryId" type="xs:unsignedInt" />

    It worked . Thanks again.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply