sql server - SSIS, XML Sources with XSD files... and nvarchar(max) destination columns -

here problem:

• i've been handed ssis package update uses xml source read data xml files , read content multiple tables.
• the xml source has xmlschemadefinition property points @ external .xsd file. .xsd file might receive updates related data source specification evolves.
• one of columns in 1 of destination tables nvarchar(max).

in xml source's advanced editor > input , output properties can manually set element's datatype properties unicode text stream [dt_ntext] , correct transformation behaviour. however, next time comes screen , clicks refresh button change undone , things start breaking.

when first given package work on .xsd element had definition:

<xs:attribute name="somelongtext" type="xs:string" use="optional" /> 

this results in datatype of unicode string [dt_wstr] , length 255, 255 being default.

ssis responds simpletype definitions, e.g.:

  <xs:simpletype name="string100">     <xs:restriction base="xs:string">       <xs:maxlength value="100" />     </xs:restriction>   </xs:simpletype>   <!-- ... -->   <xs:attribute name="somelongtext" type="string100" use="optional" /> 

this results in unicode string [dt_wstr] length 100 expected.

now, integration services data types has dt_ntext data type:

dt_ntext unicode character string maximum length of 2^30 - 1 (1,073,741,823) characters.

1,073,741,823 sounds good, relevant number use here if define simpletype restriction:

  <xs:simpletype name="stringmax">     <xs:restriction base="xs:string">       <xs:maxlength value="1073741823" />     </xs:restriction>   </xs:simpletype>   <!-- ... -->   <xs:attribute name="somelongtext" type="stringmax" use="optional" /> 

in ssis datatype of unicode string [dt_wstr] length 4000, seems maximum limit of dt_wstr.

if leverage knowledge sys.columns reports nvarchar(max) columns having length -1 , try instead:

  <xs:simpletype name="stringmax">     <xs:restriction base="xs:string">       <xs:maxlength value="-1" />     </xs:restriction>   </xs:simpletype>   <!-- ... -->   <xs:attribute name="somelongtext" type="stringmax" use="optional" /> 

i following error message in ssis when clicking refresh

error @ data flow task [xml source [229]]: there error setting mapping. maxlength constraining facet invalid - value '-1' either large or small nonnegativeinteger.

i guess rules out -1 then.

so, question is: magic need put in .xsd file coax xml source treat source element unicode text stream [dt_ntext]?

i want compatible refresh button going forward next poor schmuck having deal (possibly me) doesn't break things.

thanks , kind regards, ant.

well, pita of ssis when working standard xml source.
have manually set dt_ntext data type of output column in advanced editor , take risk can hit refresh , ruin work. standard xml source update output definition silently, have double check each time building project package.
alternative - can try commercial ssis xml source components zappysys or bluessis. have no experience of these, cannot recommend.