November 24, 2014 at 2:16 pm
I have looked at 2 ways of doing this. I really wanted to import using the XML Source and SQL Server Destination.
I Added the Data flow task, In the dataflow task I Added XML Source, Pointed it to my variable which contains an http link to the file. Created the XSD via the ui.
The XML file contains data for 5 tables. I only need to use 4 of these. So I thought I could go to inputs and out puts on the Advanced editor and click on the item and click remove output. I get an error message that an output can not be removed from an outputs collection.
So I dont worry about that at this point. I place a SQL Destination for each table after the XML Source and Align all of the columns ect, and not one for the 5th (unused) table.
I receive and error message that there is an element with the same name as the node and theres. I can not have the XML file altered, I need to be able to skip that 5th file. I am not finding an option to do it.
any suggestions?
November 24, 2014 at 2:58 pm
Quick questions, can you post an example of the XML and how big are the files?
😎
November 25, 2014 at 6:25 am
I do not have an example of the xml to post, not allowed. The file is not large.
It organizes perfectly except on the 5th table that I Need to omit. I do have a stored procedure that can access the file and place it into the 4 tables and it excludes the 5th but I do not believe that is the only way to do this it should be able to be processed in the manner that I am trying and there should be an option to exclude a node.
November 25, 2014 at 7:18 am
swoozie (11/25/2014)
I do not have an example of the xml to post, not allowed. The file is not large.It organizes perfectly except on the 5th table that I Need to omit. I do have a stored procedure that can access the file and place it into the 4 tables and it excludes the 5th but I do not believe that is the only way to do this it should be able to be processed in the manner that I am trying and there should be an option to exclude a node.
Best bet would be to review the generated XSD, suspect that's the culprit, those are normally not too clever.
😎
December 2, 2014 at 10:57 am
I tried to do the following:
1) remove the references to the extra table from the XSD = didnt work
2) Change the name of the column in the XSD still tried not to import it = Did\didnt work (sort of worked)
3) Changed the name of the column in the XSD and had it import into a table in the database = Worked
could not find any other alternative.
December 2, 2014 at 11:39 am
swoozie (12/2/2014)
I tried to do the following:1) remove the references to the extra table from the XSD = didnt work
2) Change the name of the column in the XSD still tried not to import it = Did\didnt work (sort of worked)
3) Changed the name of the column in the XSD and had it import into a table in the database = Worked
could not find any other alternative.
The thing is that although the XML structure can hold relational data in a structured format, it isn't always reverse-engineer-able, as an example, a human will know that there is only one home address related to a person, the xsd.exe will assume its a many to many relationship. This is why I discourage the use of such tools as I've never seen a satisfactory output apart from the simplest of schemas.
On the other hand, if importing the arbitrary data and ignoring it is the solution then that's probably the path of the least resistance;-)
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply