Post
Ask Question
Akhil Madivada

Akhil Madivada

  • NA
  • 58
  • 7.6k

Generating excel sheet from XML file in SSIS

Jun 2 2017 2:54 PM

Developers, I am working on the SSIS and I am having an XML data file from that XML file I want to populate required columns from that XML to EXCEL Sheet, and In that XML file there are 14 tags, from that 14 tags I have my required data in two tags, Now I have to combine the both tags and push the data coming from two tags to EXCEL Sheet , How can I create the package for combining two tags of and populate in single EXCEL sheet?

OverviewOf XML File:

There are altogether 5 Meters and for each meter in MeterReading_IntervaldataReading tag available columns are Timestamp, RawReading and Interval data and in this tag MeterSerial number is not available coming to MeterSerial Number is available in the Meter Tag, Now I have to populate in Excel sheet showing for each meter what is the RawReading available in the XML sheet with TIME Stamp column, In Excel sheet(destination) with column should be MeterSerialNUmber, Timestamp,MeterReading.

What did I do?

I took an XML source and consumed the XML file and connected to DERIVED column to see from Which tag I am getting Raw reading and time stamp this column are available in MeterReading_IntervaldataReading tag and In Serial Number is available in Metertag but both of the tags there no common column to combine. so now how can are created in SSIS?

and I am uploading my sample XML file here

  1. <?xml version="1.0"?>    
  2.     <!-- MAS Release 7.0 ( build: Jan 22 2010 19:12:10 ) -->    
  3.     <MeterReadings Purpose="OnRequestRead" CollectionTime="2013-08-09     
  4.     19:57:11" Initiator="OnRequest" SourceIrn="30"     
  5.     SourceName="coll_0591_stuart" Source="Remote" Irn="21">    
  6.   
  7.    <Meter MediaType="900 MHz" ObservesDaylightSavings="true"     
  8.    TimeZoneOffset="300" Timezone="(GMT-05:00) Eastern Time (US & Canada)"     
  9.    TimeZoneIndex="11" Location="" SdpIdent="" AccountName=""     
  10.    AccountIdent="" RemovalDate="" InstallDate="2013-05-01 20:28:09"     
  11.    Description="" MeterType="REX" SerialNumber="03310266" IsActive="true"     
  12.    MeterName="03310266" MeterIrn="21"/>    
  13.   
  14.   <ConsumptionData>    
  15.   <ConsumptionSpec Multiplier="1"     
  16.   MeasurementPeriod="EndOfIntervalSnapshot" TouBucket="Total"     
  17.   Direction="Delivered" UOM="kWh"/>    
  18.   <Reading Value="73180" TimeStamp="2013-08-09 00:00:00"/>    
  19.   </ConsumptionData>    
  20.   
  21.   <ConsumptionData>    
  22.   <LoadProfileSummary>    
  23.   <IntervalData>    
  24.   <IntervalData>    
  25.   <IntervalSpec Multiplier="0.1" Direction="Received" UOM="kWh"     
  26.   TimestampEnd="2013-08-09 00:00:00" TimestampStart="2013-08-02 00:15:00"     
  27.   Channel="2" Interval="15"/>    
  28.   <Reading TimeStamp="2013-08-02 00:15:00" RawReading="0"/>    
  29.   <Reading TimeStamp="2013-08-02 00:30:00" RawReading="0"/>    
  30.   <Reading TimeStamp="2013-08-02 00:45:00" RawReading="0"/>    

Answers (1)