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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics