Extract XML data fro Excel Column
I have the following xml data in excel column, how do i extract data from it to a table using ssis.
Column name XT_BODY
<SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP>10.21.252.2</_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421
</DEVID><SOURCEID>RPCC</SOURCEID></SendData>
November 20th, 2010 7:19am
Hi,
As
you put it
does not seem
very
complicated, maybe there are
other requirements.
To
read the
Excel
file using
the
Excel Source
component
and to insert
the
rows using
the
OLEDB Destination
component.
You can
change the data type using
a
Data Conversion.
Maybe you need parse the XML to get the field tags as columns. If is this, tell us.Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
November 20th, 2010 3:03pm
That is exactly what I need please do show a sample on how to archive that in ssis. This is how my data looks in excel
XT_MESSAGE
XT_STAMP
XT_BODY
t_DPS_Prod_Qty
11/16/2010 11:43:00
<SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP>10.21.252.2</_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421
</DEVID><SOURCEID>RPCC</SOURCEID></SendData>
XT_MESSAGE XT_STAMP XT_BODY
t_DPS_Prod_Qty 11/16/2010 11:43:00 "<SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP>10.21.252.2</_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421 </DEVID><SOURCEID>RPCC</SOURCEID></SendData>
"
November 21st, 2010 10:30am
That is exactly what I need please do show a sample on how to archive that in ssis.
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2010 10:31am
SSIS is not as simple as using a Import Export Wizard. You should start from some basics.
http://blogs.techrepublic.com.com/datacenter/?p=205
November 21st, 2010 11:40am
Hi... you already try the Excel Source?Vctor M. Snchez Garca (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 2:55am