April 17, 2014 at 2:41 pm
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,
April 17, 2014 at 2:49 pm
Is there a value in the MemberRole element that can give an overflow for a bigint?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 17, 2014 at 5:45 pm
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?
April 17, 2014 at 7:55 pm
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?
April 18, 2014 at 5:54 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply