|
|
You're in the
sql server dts
group:SSIS can't import from XML schema
sql server dts:
I am trying to import XML data trough external XML schema file. The schema is
valid and I was able to create relational database based on it. When importing XML data through SSIS, I get an error message saying that schema contains invalid characters /, \, . etc. The reason, my schema has dots "." it filed names. The schema comes from external application which I have no control over. Do you have any suggestions as how can I avoid the error in SSIS. Thanks in advance,
Hello zk_,
Please post the schema so we can see it. I'm not sure what "field" names means here, or why would have slashes in whatever they are. :) Thanks! Kent Tegels DevelopMentor http://staff.develop.com/ktegels/
Hello Zoran,
As Kent mentioned, will you provide a sample schema and data file so that we could reproduce the issue on our side? Thank you. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at <http://msdn.microsoft.com/subscriptions/support/default.aspx>. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Peter and Kent,
Thank you for your replies. What I mean by "field names" is - elements. So when I specify the XML source to import from in SSIS, I get to specify the xml file below, and then the xsd file (also listed below). This bring up the error about invalid characters in the schema. So the "." in "ship.to" is causing this. As I said, these files are created by a third party I we can not control it. Any thoughts how can I overcome this in SSIS? The sample is listed below, many thanks for your time. Zoran -------xml------------------------------------------------------------------------------ <?xml version="1.0" encoding="ISO-8859-1"?><shiporder orderid="889923" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="shiporder.xsd"> <orderperson>John Smith</orderperson> <ship.to> <name>Ola Nordmann</name> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </ship.to> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder> ------------------------------------------------------------------------------------------ ---------xsd----------------------------------------------------------------------------- <?xml version="1.0" encoding="ISO-8859-1" ?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:element name="shiporder"> <xs:complexType> <xs:sequence> <xs:element name="orderperson" type="xs:string"/> <xs:element name="ship.to"> <xs:complexType> <xs:sequence> <xs:element name="name" type="xs:string"/> <xs:element name="address" type="xs:string"/> <xs:element name="city" type="xs:string"/> <xs:element name="country" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="item" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="title" type="xs:string"/> <xs:element name="note" type="xs:string" minOccurs="0"/> <xs:element name="quantity" type="xs:positiveInteger"/> <xs:element name="price" type="xs:decimal"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="orderid" type="xs:string" use="required"/> </xs:complexType> </xs:element></xs:schema> ------------------------------------------------------------------------------------------ [quoted text, click to view] ""Peter Yang[MSFT]"" wrote:
> Hello Zoran, > > As Kent mentioned, will you provide a sample schema and data file so that > we could reproduce the issue on our side? Thank you. > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Community Support > ================================================== > Get notification to my posts through email? Please refer to > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif > ications > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues > where an initial response from the community or a Microsoft Support > Engineer within 1 business day is acceptable. Please note that each follow > up response may take approximately 2 business days as the support > professional working with you may need further investigation to reach the > most efficient resolution. The offering is not appropriate for situations > that require urgent, real-time or phone-based interactions or complex > project analysis and dump analysis issues. Issues of this nature are best > handled working with a dedicated Microsoft Support Engineer by contacting > Microsoft Customer Support Services (CSS) at > <http://msdn.microsoft.com/subscriptions/support/default.aspx>. > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Kent,
Thank you for your reply. This is not going to be an easy workaround for us. The XSD file is huge, but that can be changed to eliminate full stops. However, we'll need a tool to convert our xml files. Since these files are automatically generated, we'll need to incorporate automated XSLT conversion in SSIS package. Is there a good XSLT convarsion tool suitable for this kind of task? Regards, Zoran Knezic [quoted text, click to view] "Kent Tegels" wrote:
> Hello Zoran, > > This looks to be idiosyncratic behavior of the SSIS XML data source. Section > 2.3 of the XML spec allows full-stop character in element names. and interestingly > enough, the XML task validates your XML with the schema. > > Since the XML task seems to like the combination of XML and Schema, the "easiest" > way I can see around this problem would be to write an XSLT that produces > another XML file to work with. > > I've put in a bug report for this, but I suspect that its going to get dismissed > as a security (preventing directory transversal) "feature." For whatever > reason, its not showing up in connect yet, but if you go check there in a > while searching on "xml source full stop" you should (hopefully) find it. > > PETER: can you check to see if this is showing up in Ladybug for SQL 2005? > > Thanks! > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > >
Hello Zoran,
This looks to be idiosyncratic behavior of the SSIS XML data source. Section 2.3 of the XML spec allows full-stop character in element names. and interestingly enough, the XML task validates your XML with the schema. Since the XML task seems to like the combination of XML and Schema, the "easiest" way I can see around this problem would be to write an XSLT that produces another XML file to work with. I've put in a bug report for this, but I suspect that its going to get dismissed as a security (preventing directory transversal) "feature." For whatever reason, its not showing up in connect yet, but if you go check there in a while searching on "xml source full stop" you should (hopefully) find it. PETER: can you check to see if this is showing up in Ladybug for SQL 2005? Thanks! Kent Tegels DevelopMentor http://staff.develop.com/ktegels/
Hello zk_,
[quoted text, click to view] > Thank you for your reply. This is not going to be an easy workaround > for us. I feel your pain. [quoted text, click to view] > The XSD file is huge, but that can be changed to eliminate full stops. Ok. [quoted text, click to view] > However, we'll need a tool to convert our xml files. Since these files are automatically generated, we'll need to incorporate automated XSLT conversion in SSIS package. Is there a good XSLT convarsion tool suitable for this kind of task? Altova's MapForce [0] might be able to generate the XSLT, then you could just have an XSLT task in your package that does the conversion. [0] http://www.altova.com/products/mapforce/data_mapping.html OTOH, it might be appropriate to just use a text-editor to make a copy of the XSD and remove the full stops. You could then write an Script Task that chewed line-by-line through the xml tags with a fullstop using a regex and doing a replacment. Ah if only Perl.NET was acceptable for a Script Task... :) Thanks! Kent Tegels DevelopMentor http://staff.develop.com/ktegels/
Hello Zoran,
As described in the error message "The object name ship.to is not valid. The name cannot contain any of following characters:/\:[].=", this is a product limitation of SSIS for XML source. I agree with Kent that you could use "XSL task" in SSIS to do the XSLT conversion or you could use Script task to do this job by code. As for creating XSLT file, if you have Biztalk installed, you could do this easily in Biztalk Mapper via VS 2005. Also, there are some third party tool that you could use as suggested by Kent. Zoran, please rest assured that your feedback on this issue is routed to the proper channel. Also you could submit via the link below and our product team would like to hear your voice: http://lab.msdn.microsoft.com/productfeedback/default.aspx Please let's know if you have any further questions or concerns. Thank you! Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ======================================================
Kent and Peter,
Thank you both for your time. Your help is very much appreciated. Zoran Knezic [quoted text, click to view] "Kent Tegels" wrote:
> Hello zk_, > > > Thank you for your reply. This is not going to be an easy workaround > > for us. > > I feel your pain. > > > The XSD file is huge, but that can be changed to eliminate full stops. > > Ok. > > > However, we'll need a tool to convert our xml files. Since these files > are automatically generated, we'll need to incorporate automated XSLT conversion > in SSIS package. Is there a good XSLT convarsion tool suitable for this kind > of task? > > Altova's MapForce [0] might be able to generate the XSLT, then you could > just have an XSLT task in your package that does the conversion. > > [0] http://www.altova.com/products/mapforce/data_mapping.html > > OTOH, it might be appropriate to just use a text-editor to make a copy of > the XSD and remove the full stops. You could then write an Script Task that > chewed line-by-line through the xml tags with a fullstop using a regex and > doing a replacment. > > Ah if only Perl.NET was acceptable for a Script Task... :) > > Thanks! > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > >
Hi,
There has been another rather strange development related to this issue... We were able to get out xml source reformatted through XSLT, and get to the format which seems to be working OK. The issue now is that Data Flow task that reads from the new xml file (with separate referenced schema) hangs, without returning any error messages. We tested and are pretty confident that the problem is the actual complexity of the schema. The schema consists of about 100 tables, one table referenced by another 30-40 tables, each linked to another 0-6 tables each. We did try testing the data flow task with trimmed down version of the xml file and it did not help. We also tried slimming down the schema (dropped 50-60 tables) and it worked! So it does sound like we are hitting some sort of limit. Does anyone have any thoughts on this. Is it SSIS handling of schema limitation or is it hardware limitation. We were testing this on a 1 Gb, 2.8 Ghz desktop. Thanks, Zoran Knezic [quoted text, click to view] ""Peter Yang[MSFT]"" wrote:
> Hello Zoran, > > As described in the error message "The object name ship.to is not valid. > The name cannot contain any of following characters:/\:[].=", this is a > product limitation of SSIS for XML source. > > I agree with Kent that you could use "XSL task" in SSIS to do the XSLT > conversion or you could use Script task to do this job by code. > > As for creating XSLT file, if you have Biztalk installed, you could do this > easily in Biztalk Mapper via VS 2005. Also, there are some third party tool > that you could use as suggested by Kent. > > Zoran, please rest assured that your feedback on this issue is routed to > the proper channel. Also you could submit via the link below and our > product team would like to hear your voice: > > http://lab.msdn.microsoft.com/productfeedback/default.aspx > > Please let's know if you have any further questions or concerns. Thank you! > > Best Regards, > > Peter Yang > MCSE2000/2003, MCSA, MCDBA > Microsoft Online Partner Support > > ====================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |