May 28, 2011 at 6:53 pm
I have to do this using SSIS( BIDS) business integration tool. (DTS Transformations)
I have to take XML file as a source file and get same XML file as a output file with different format.
The input format of the XML file is shown below...
<key> ABC <\key>
<value>456<\value> like these i have so many rows
and i need output file as...................
<ABC>456<\ABC>
I have to do this using ssis( BIDS) business integration tool
i need suggestion, i need to take XML source from data flow source are or i need to take XML task from control flow task. What type of transformation i have to use.
May 28, 2011 at 9:50 pm
Because your source XML file is sequential (you 've got key-value pairs) you need to peek ahead to compose a single row in the destination XML file based on two rows in the input.
This peek ahead represents a slight problem that you I think can overcome by using a Conditional Split http://www.bimonkey.com/2009/06/the-conditional-split-transformation/
to redirect 1 row to table say named Key and another to table Value.
So your tables will look like:
Key Value
---- ------
ABC 456
DEF 789 etc.
Now knowing that each row in these tables is a single record for your target XML file you can either use FOR XML AUTO T-SQL (http://blah.winsmarts.com/2007-2-Bak2Basics__Learn_T-SQL_-_FOR_XML.aspx) to get the file, or use Execute SQL Task to to feed a Script Component that will generate this XML file like described here: http://agilebi.com/jwelch/2007/06/02/xml-destination-script-component/
May 29, 2011 at 2:36 am
Is this some sort of homework?
If not, why do you have to use SSIS?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply