0
Answer

Creating package in SSIS for XML file to store data in DB.

Akhil Madivada

Akhil Madivada
7y
593
1
  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"/>  
Developers, I am working on SSIS and I want to create a package to populate the XMLdata in the database from an XML source file and my source file sample data is shown above.
 
In the database table I have columns TimeStamp column, Meterdirection column, MeterName and I need the both values to populate in the columns of the table and there is one more table in available in the database with Meterdirection type with Delivered=1 and Received=2, So now how can I create the package in SSIS can anyone help me? 
 
What I did: created a package inSSIS to Extract the XML file using the XML source and for the Load OLEDB Destination and in transformation I used the Derived column to get required column but in the destination table, MeterDataId and MeterName are not populating.
 
 
Next Recommended Forum