SQL Clone
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-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28567 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12857 Visits: 18584
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)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12857 Visits: 18584
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15468 Visits: 9006
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