Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue with SSIS Package with XML Source Task


Issue with SSIS Package with XML Source Task

Author
Message
QQ-485619
QQ-485619
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 278
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,
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16372 Visits: 13199
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7630 Visits: 18043
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?
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7630 Visits: 18043
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?
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7745 Visits: 8719
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search