Importing XML files to SQL Database using SSIS.

[Database Administrator]

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.

[Database Administrator]

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.

[Database Administrator]

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.
>
>
>

[Database Administrator]

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/

[Database Administrator]

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.

[Database Administrator]

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.
>
>
>
>
>

[Database Administrator]

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.

[Database Administrator]

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.
>
>
>
>
>

[dstiles74]

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

[WizardDB]

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