SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import XML Data into SQL Server database using SSIS package?????


Import XML Data into SQL Server database using SSIS package?????

Author
Message
TonyP
TonyP
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 56
Is this even possible? I looked everywhere. I can't find a tutorial to save my life. All I found on internet are a bunch of unanswered questions like mine. Can someone point me in the right direction?
Tim Mitchell
Tim Mitchell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 2988
In the SSIS data flow task, there is an XML Source component which allows you to specify your source XML file and optional XSD schema. From there you can pipe the data into a SQL Server destination.

hth,
Tim



Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


TonyP
TonyP
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 56
Hello,

Thanks for the reply. What I ended up doing was purchasing the book Professional SQL Server 2005 Integration Services. I found the answer in there, but I ran into a new problem that doesn't seem to be addressed by the book.

Initially after running my package, I got an error on a couple of my fields stating that an unicode string could can not be convert to a non-unicode string. I solved this issue by changing the datatypes in my database from varchar to nvarchar.

However in the near future, I am not going to have a choice to change the datatypes in the database. Is there another way of handling situations like this within the package itself?
Tim Mitchell
Tim Mitchell
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 2988
You bet... what you want is the Data Conversion component. Stick it between your XML source and your SQL destination in your Data Flow, and you can convert Unicode to ASCII or vice versa.

Tim



Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


aliang
aliang
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
I am also trying to import a xml file to SQL tables using SSIS Data Flow Task --> XML source
--> OLEDB destination. I provided the xml file and a xsd file in the XML souce editor.

The extraction of the XML file will created 5 tables. My problem is that one of the tables
is empty. Any idea how to solve this problem? I have tried to add a "root" node to the
XML file, and it worked. Is there any other way to solve this problem without manually
changing the XML file?

Thanks!
TonyP
TonyP
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 56
Hello

Actually, I haven't gotten this far myself. I ran into issues getting my XML file to validate using a provided XML schema. While trying to get this to work using C# code, I realized that I needed multiple schema files in order for it to work.

It looks like I ran into a limitation of the XML Source. It only allows you to use one XML schema file. I need to use multiple files.
ranjitk
ranjitk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 3
Believe I too had this problem where one schema depended upon another one. I saved a copy of the second schema in the same location and it worked.

Issue I am facing is that I need to update a table based on information from the xml-source; lookup field in the xml source is in one logical group, while the source for update is in another. God save me, or may be an mvp/guruWink
Radoslaw Jaszek
Radoslaw Jaszek
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Hello,

Can anyone help me with importing xml file that contains inline schema ? Any example, please...

Many thanks.
ronnieperetz
ronnieperetz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
what page in the book tell you how to get the relationship to work between the 5 tables?
I getting a hard time getting the relations of "one to many" to work in my output sql tables
ramesh.sripathi-823300
ramesh.sripathi-823300
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 141
Hi

I am working on SSIS

I have an XML file that consists of nearly 5 tables of data,I have one XSD generated based on the XML file.

I need to populate the data present in XML file into corresponding five tables

Please help me out how to resolve this issue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search