| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2006-05-31 : 04:35:35
|
| I'm beginning to think that I should get my own forum - Wanderer's inane, novice SSIS questions!In my sink-or-swim-and-batch-this-car new job, I've suddenly been asked to look at taking an XML file, merge it with another XML file and a csv file, and spit out a formatted XML file (this file has a dtd, whereas the other's don't have a defined schema, because they are dynamically created based on changing metadata from the source system). Fortunately I have until next week to do this - so the question is will it be faster to try and learn how to do this, or try to design and create a time machine?Search for XSLT and XML Transformation hasn't been very fruitful in SQLteam. So, can anyone point me to decent articles/tutorials/walk-throughs to get me running here?Thanks (...and pray for me!)*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-31 : 05:26:58
|
why use SSIS for this??use C# and it's XmlDocument object.you can do all sorts of cool stuff with that.Go with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-31 : 05:35:49
|
| Thanks Spirit,While I'll start asking the question, I believe we are tied into this as being the solution that the client wants - all integration services to be done using SSIS and BizTalk 2006.Of course, it would be a good reason to dive into learning C# ... but as always, I suspect clients wants (driven by their architect, I believe), and time constraints, will be the whip for this poor donkey.Out of interest, other than the ability to do it in c# - what would the benefits of this be, over using the XML tasks in SSIS? Have you done this, and foudnthis to be better/more efficient etc.? Those kind fo arguements are likely to carry more weight with our PM and the client's architect than just saying it's an option. ((Please don't get me wrong - I'm not criticizing the idea, just trying to flesh out some detail for it as an alternative).*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-31 : 06:10:11
|
no i haven't done any xml manupulation in SSIS.I try to avoid SSIS as much as i can  In another post by blindman in this fourm we learned that there is no XML destination in SSIS. so you can have the XML source but you can't output your stuff in XML. go figure...in c# i've done a bit of XML stuff. i like it. it's flexible and quite quick...Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-31 : 09:07:43
|
| Link the the 'blindman' post? I assume it's this one:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65534&SearchTerms=xmlhmm - that's pretty ugly. Is there no way of using the flat file destination to write out the XML file? I'll have a look at that, else I may have to kick this back to the drawing board, and have them do this is C# or BizTalk...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-31 : 09:28:48
|
| yes it's that one... sorry for not posting it... didn't have time to find it.in c# this is done in a day if you ask me.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-31 : 09:43:14
|
| I linked an old post of mine to blindman's thread that may help you out.One thing I've found with XML sources in SSIS is that I always had to re-do the column mappings on each file, even if the XSD file was the same and the formats were identical. I have a feeling it's due to the BI interface, but I couldn't get a ForEach container to enumerate the files and designate them the source. I decided to use another old trick, which is to rename each file to a standard name for import, then rename it back afterwards. I still didn't get it to work but it should at least preserve column mappings. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-31 : 11:07:16
|
| Thanks Rob - I'm feeling like we are trying to use an aeroplane to boxes around inside a war-house.... we might be able to find a way to do it, but should we - I think spirit has the right idea, but (a) being new to the company <2 weeks>, (b) knowing very little about SSIS, XML, XSLT or C# <beginning to wonder if they read by CV before signing me up, or thought about what it was they wanted> - I'm finding it quite difficult to make any real headway.One of the programmers has been looking at the problem with me (I only heard about this a day ago), and thinks that with the appropriately written XSLT, we could take an XML file, XSLT it, and direct the result to a text file, that would be an XML file since the XSLT would format the results as such*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-31 : 11:41:27
|
| hey wandere.. if it's possible could you give an example of what you're trying to do? with real data and stuff.i'm curious...Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-31 : 12:00:13
|
| I'm trying to get real data myself. To outline, at the moment:We have an application that will generate an XML file, for products. This product file may have a changing schema, since the application allows users to enrich the data around a file, adding and modifying attributes.That XML file (or file's - the code to generate it hasn't been finished yet) then needs to be taken from source location, merge in data from another (client) system that contains stock availability, in a (as yet undetailed) .csv file. That merged file, covering the product and stock detaisl, needs to then be converted into an xml file, defined by a specific dtd, and placed on a target location. That file will be picked up, and used, from that target location, for use by a 3rd party application (they are the ones that defined the dtd requirement for the input file).The entire above is currently being defined as work that needs to happen in the 'integration services' space of the enterprise architecture - and that space has been defined as using either Biztalk 2006 or SSIS.As you may have inferred, or I may have stated - no actual files exist as yet, and some of the code that is supposed to generate these files is still in design phase - it looks like we've been able to push back the inital requirement to have the single source system write out 2 sets of related XML files, and have SSIS merge those files, and rather have the source system do that merge and push out one combined, synchronized file.Finally, being very new to the company, and this being a high profile task for the client, I suspect I'd be in pretty hot water if I distributed names and data - but I'm not sure atm.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-05-31 : 12:20:09
|
| well i can't even begin to think about how this would be done in SSIS.but in C# it's preety simple Load XmlDocument with original data.write some logic how to merge hte CSV.Create a new XmlDocument and put defined schema on it.Load merged xml into new document.Not counting merge logic it's about 20-30 lines of code... Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-05-31 : 12:52:34
|
| to paraphrase a much-abused movie quote:"You had me at c#...."I've made some inadvertant progress, using a sample I found on the web, doing - if I understand it correctly - xsl:copy to move my XML data from the source to target. The target is a txt file (atm), which seems very much like my original file. Doesn't mean it will be completely possible - still looking at it...Thanks again - now I just need to do enough leg-work/brain-0work to convince the powers-that-be that C# is the way to go...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
XML transformation in SSIS