Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Import XML Data into SQL Server database using SSIS package????? Expand / Collapse
Author
Message
Posted Thursday, March 13, 2008 3:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 7:33 AM
Points: 13, 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?
Post #469057
Posted Thursday, March 13, 2008 7:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #469110
Posted Thursday, March 13, 2008 9:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 7:33 AM
Points: 13, 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?
Post #469154
Posted Thursday, March 13, 2008 9:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:19 AM
Points: 1,046, Visits: 2,739
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #469156
Posted Tuesday, April 1, 2008 10:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 19, 2008 6:08 AM
Points: 1, 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!
Post #478225
Posted Wednesday, April 2, 2008 8:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 7:33 AM
Points: 13, 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.
Post #478579
Posted Tuesday, April 15, 2008 2:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 3:49 PM
Points: 1, 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/guru;)
Post #485301
Posted Thursday, June 12, 2008 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2008 12:50 PM
Points: 1, Visits: 2
Hello,

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

Many thanks.
Post #516204
Posted Thursday, December 18, 2008 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 30, 2008 9:40 AM
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
Post #622573
Posted Tuesday, May 5, 2009 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:43 AM
Points: 2, Visits: 139
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
Post #710250
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse