Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with SSIS Package with XML Source Task Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:30 AM
Points: 37, Visits: 245
I have a SSIS Data Flow Task which load a XML file into a table. While working with a XML file of 1.2M, I don't have any problem. However, with a XML file of 16 M, I get the following error:

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

In the table, this column is bigint and I used eight byte signed integer in the external and internal column properity.

Can somebody help?

Thanks,
Post #1562857
Posted Thursday, April 17, 2014 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
Is there a value in the MemberRole element that can give an overflow for a bigint?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1562864
Posted Thursday, April 17, 2014 5:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
You may also want to check the metadata in SSIs to see if it "guessed" incorrectly as to the underlying data types. If it had to derive the types rather than getting from an authoritative source - it probably "guessed" that that column is supposed to be a 2-byte integer.


To see what it picked - open up the properties on the xml file connector, one of the tabs there shows what was done (input/output types as I recall). You can then override the guess to an appropriate data type (pretty sure there is a flag you can throw on the connection string to prevent it from guessing but I cannot recall what that is right now).


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1562885
Posted Thursday, April 17, 2014 7:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
Sorry - that's the "Show Advanced Editor". As in - right-click the flat file or XML source object in your data flow, then "Advanced Editor". Check the data types listed in both sections of the "source output" section, located on the Input and output properties tab.

One of those is likely set to DT_UI2 unsigned, two-byte integer, which has a max value of 32767 (i.e. less than the value listed in your error).


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1562894
Posted Friday, April 18, 2014 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
I force an XML source to generate an XSD when first adding a new file, then open up the XSD in TextPad or another editor to verify all the datatypes are correct. This is probably where your problem is. SSIS didn't read far enough into the file when checking for datatypes or you used the wrong file (one with numbers too short) to generate your initial XSD.

Try regenerating it with the failing file and see if that fixes your issue.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse