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>

Have u try XML Data Type Please see following link http://msdn.microsoft.com/en-us/library/bb510446.aspx Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

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>

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

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> "

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

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