+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Importing XML files to SQL Database using SSIS.

  1. Importing XML files to SQL Database using SSIS.

    Hi,

    I need to Import an XML file in to SQL Database using SSIS, Plz let me know
    how do I acheive this. I am quite new to SSIS, So if you can Point me to some
    samples on importing XML files using SSIS, nothing like that. Any additional
    references, if any, are most welcome.

    Thanks in Advance,
    Sachin R. Chavan.

  2. RE: Importing XML files to SQL Database using SSIS.

    Hello Sachin,

    I understand that you'd like to import an XML file into SQL server.

    You could use XML source directly in your data flow to import data in XML
    into a SQL table. Please see the following articles for details:

    XML Source
    http://msdn2.microsoft.com/en-us/library/ms140277.aspx

    Sourcing XML Data in SSIS 2005
    http://blogs.msdn.com/rdoherty/archi...16/396956.aspx

    Actually, if it is a xml file, the step is very straightfward:

    1. Create a new SSIS project in VS2005
    2. Create a new data flow.
    3. Drag "xml source" from toolbox into data flow panel, select the xml
    file, and then generate XSD schema.
    4. You could drag a "SQL Server detination" to the panel as data
    destination, connect XML source and SQL Server Destination, and use a new
    table a

    If you encouner any issues, please free to let me know. 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/subscripti...ult.aspx#notif
    ications
    .
    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
    .
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.



  3. RE: Importing XML files to SQL Database using SSIS.

    Hi Peter,

    Your reply was really helpful. But I am facing a new problem now.

    I have an xml file that is having somewhere around 50-60 fields and I dont
    hv a schema for the same so I am generating a schema by clicking 'Generate
    Schema' button on XML Source Editor of SSIS.

    Next, I am also Genrating a 'new table' from the 'SQL Server Destination'
    Properties box.

    Now, tough I have the matching columns in the new generated table I am
    getting Package Validation Error... basically a Type Conversion mismatch
    Error saying... Conversion betn DTUI8 & DT_Numeric is not supported.

    There are lot many such coloumns.. where do I get SQL Server 2005 n XML
    supported type conversion document n... I guess I need to change the types in
    XML Schema for this or in SQL table to make it compatible to insert the data.

    Request you to fwd me the Supported type conversion doc or if it is
    available somewhere online, plz send me the url.

    Thanks,
    Sachin R. Chavan.

    "Peter Yang [MSFT]" wrote:

    > Hello Sachin,
    >
    > I understand that you'd like to import an XML file into SQL server.
    >
    > You could use XML source directly in your data flow to import data in XML
    > into a SQL table. Please see the following articles for details:
    >
    > XML Source
    > http://msdn2.microsoft.com/en-us/library/ms140277.aspx
    >
    > Sourcing XML Data in SSIS 2005
    > http://blogs.msdn.com/rdoherty/archi...16/396956.aspx
    >
    > Actually, if it is a xml file, the step is very straightfward:
    >
    > 1. Create a new SSIS project in VS2005
    > 2. Create a new data flow.
    > 3. Drag "xml source" from toolbox into data flow panel, select the xml
    > file, and then generate XSD schema.
    > 4. You could drag a "SQL Server detination" to the panel as data
    > destination, connect XML source and SQL Server Destination, and use a new
    > table a
    >
    > If you encouner any issues, please free to let me know. 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/subscripti...ult.aspx#notif
    > ications
    > .
    > 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
    > .
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >


  4. RE: Importing XML files to SQL Database using SSIS.

    Hello Sachin,

    > There are lot many such coloumns.. where do I get SQL Server 2005 n
    > XML supported type conversion document n... I guess I need to change
    > the types in XML Schema for this or in SQL table to make it compatible
    > to insert the data.


    Use the "data conversion" component in a data flow.

    Thanks!
    Kent Tegels
    DevelopMentor
    http://staff.develop.com/ktegels/



  5. RE: Importing XML files to SQL Database using SSIS.

    Hello Sachin,

    It seems that schema created automatically does not match the type in SQL
    table. As Kent mentioned, you could use "data conversion" component to
    convert the field that does not have the proper data type.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/4aabbe4f-7666-4672-865a-9
    627bd25fbfd.htm
    How to: Convert Data to a Different Data Type Using the Data Conversion
    Transformation

    Integration Services Data Types
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/896fc3e8-3aa6-4396-ba82-5
    d7741cffa56.htm

    DataType Enumeration
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer
    _Dts_Runtime_Wrapper_DataType.htm

    Cast (SSIS)
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d4e915cc-1c7b-4b2e-93b0-1
    3a8b0cb9242.htm

    Also, you may try to change datatype of column in XML source by using the
    following steps:

    1. Right click the XML source->Show Advanced editor->Input and Output
    properties
    2. Expand inputname->External Columns->Column name
    3. On right panel, try to change the Datatype directly.

    If you hyave any further questions, please feel free to let's know. Thank
    you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    =====================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.





  6. RE: Importing XML files to SQL Database using SSIS.

    Hi Peter/Kent,

    I was successful to use Data Conversion component.
    The things are working now... except one thing my xml has got one filed
    whose min occur is zero... meaning that this filed may or may not appear in a
    record. Now this field has got huge number of characters in it n type of this
    field is set to xs:string.

    XSD element Defn:


    Xml encoding is set to "ISO-8859-1" & Actual XML Data in the filed is as
    follows:

    A 15-century sculpture of the Virgin links these three
    alternating stories, all set amidst the mystery and splendor of Venice. Into
    three men's lives, and over five centuries, the stone madonna brings an equal
    amount of sexual ecstasy and misery; she's really a bloodless monument to the
    old *****/virgin complex - a dichotomy still rich in meaning for the rather
    bloody-minded Unsworth (author most recently of The Idol Hunters and The Rage
    of the Vulture). Pity poor Girolamo Satta, the luckless sculptor of the
    unheralded masterpiece. His lengthy letter to his patron begging help - here
    reproduced in full - reveals both the reason why he's in jail and soon to be
    hanged and also why his work will later languish in obscurity. It seems he's
    charged with murdering his model, Bianca the prostitute, whom he's known
    quite well in the biblical sense, often while she was still in New Testament
    garb. Small wonder, then, that Simon Raikes, restoring the holy icon in 1972,
    discovers a strange sensation emanating from it. As he removes the
    accumulated grime, he grows wildly priapic, aroused even by his grandmotherly
    landlady. He's especially tumescent, though, for the mysterious Chiara
    Litsov, an artist's wife, who may or may not provide clues to the statue's
    unusual provenance. Between the sculptor's and restorer's narratives,
    Unsworth slips in the erotic history of another rake, the destitute
    18-century patrician Ziani, hard at work on his scandalous memoirs, lengthy
    excerpts of which record his encounter with the madonna. Under her gaze, he
    deflowered her wealthy old owner's virgin bride. While the main plot -
    Raikes' exploration in sacred and profane lust - dissolves into a
    deliberately open-ended mystery, the other two sad stories end in predictable
    tragedy. Both Girolamo's prison letters and Ziani's death-bed memoirs never
    reach their intended audiences; they're intercepted by members of the same
    evil-minded Venetian family that spawned Raikes' heart-throb, the ruthless
    Mrs. Litsov, nee Fornarini. The book's fundamental implausibility -
    Unsworth's recreation of supposedly destroyed documents - shouldn't deter
    readers on the lookout for some lightly instructional, solidly middlebrow
    fiction. (Kirkus Reviews)


    Now, If I supply records without this filed they get inserted into database
    very smoothly without any errors... but if this field is present in record
    the the package get bloced on XML Source component itself.. throwing some
    truncation problem.

    Can u guide me what is wrong with this?
    Is the xml encoding the source of problem or is it that some characters in
    this field are not
    supported by xml or the parsing component in SSIS?

    Looking fwd for ur inputs.

    Thanks,
    Sachin R. Chavan
    "Peter Yang [MSFT]" wrote:

    > Hello Sachin,
    >
    > It seems that schema created automatically does not match the type in SQL
    > table. As Kent mentioned, you could use "data conversion" component to
    > convert the field that does not have the proper data type.
    >
    > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/4aabbe4f-7666-4672-865a-9
    > 627bd25fbfd.htm
    > How to: Convert Data to a Different Data Type Using the Data Conversion
    > Transformation
    >
    > Integration Services Data Types
    > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/896fc3e8-3aa6-4396-ba82-5
    > d7741cffa56.htm
    >
    > DataType Enumeration
    > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer
    > _Dts_Runtime_Wrapper_DataType.htm
    >
    > Cast (SSIS)
    > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d4e915cc-1c7b-4b2e-93b0-1
    > 3a8b0cb9242.htm
    >
    > Also, you may try to change datatype of column in XML source by using the
    > following steps:
    >
    > 1. Right click the XML source->Show Advanced editor->Input and Output
    > properties
    > 2. Expand inputname->External Columns->Column name
    > 3. On right panel, try to change the Datatype directly.
    >
    > If you hyave any further questions, please feel free to let's know. Thank
    > you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Partner Support
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > =====================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    >
    >


  7. RE: Importing XML files to SQL Database using SSIS.

    Hello Sachin,

    From the error message, it seems the type of column of SQL table is not
    defined properly. You may want to check the data type of the column to see
    if it has the enough length. Since the length is almost 3000 characters,
    you may want to use varchar(3000) to see if this resolves the issue.

    If you have any update, please let's know. Thank you.

    Best Regards,

    Peter Yang
    MCSE2000/2003, MCSA, MCDBA
    Microsoft Online Partner Support

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    =====================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.





  8. RE: Importing XML files to SQL Database using SSIS.

    Hi Peter,

    You guessed it right, the problem was with the length of characters. My type
    was supporting only 255 characters n the XML was having 3000+ charcters. This
    caused the overflow n was the source of error.

    Thanks for the Tip,
    Sachin R. Chavan.

    "Peter Yang [MSFT]" wrote:

    > Hello Sachin,
    >
    > From the error message, it seems the type of column of SQL table is not
    > defined properly. You may want to check the data type of the column to see
    > if it has the enough length. Since the length is almost 3000 characters,
    > you may want to use varchar(3000) to see if this resolves the issue.
    >
    > If you have any update, please let's know. Thank you.
    >
    > Best Regards,
    >
    > Peter Yang
    > MCSE2000/2003, MCSA, MCDBA
    > Microsoft Online Partner Support
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > =====================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    >
    >


  9. Re: Importing XML files to SQL Database using SSIS.

    Hi Peter,

    Sorry to hijack this discussion but I am experiencing very similar problems. I have a basic SSIS package importing a flat xml file into sql server. I have an xsd file that I've written. Everything is validated and appears fine. When I run the SSIS package, the package completes successfully but imports no data! Because I can find no errors I am unsure how to proceed. Any advice would be much appreciated.

    Thanks in advance.

    Darren

  10. Re: Importing XML files to SQL Database using SSIS.

    Hi Peter,

    This has ben very useful. However when I try to generate the XSD, I get a messge "For security reasons DTD is prohibited in the XNML document. To enable DTD procerssing set the ProhibitedDtd property on XMLReader Settings to false and pass the settings into XmlReader. Create method."

    Can you please let me know wher I can set this property to false.

    Many thanks

+ Reply to Thread
Page 1 of 2 1 2 LastLast