import xml using SSIS

• arun_kumar14

  SSC Enthusiast

  Points: 109

  More actions

  December 8, 2010 at 10:55 am

  #231999

  Hi,

  I need a help here....

  need to import below xml file using SSIS package.

  Each of the banking centers has multiple users. and the data should split here into 2 tables (sql server is the database)

  banking centers should go to table1 and users to table2.

  I created a simple SSIS package to import data from simple XML file with 3 columns (Fst_name, Lst_name, Age) and I am able to load onto one table. but here my requirement is data has to go into two tables.

  this requirement is very urgent, please help me out on this.

  below is the sample file

  <BankingCenters>

  <BankingCenter>

  <BankingCenterID>14213</BankingCenterID>

  <BankingCenterName>West End</BankingCenterName>

  <AddressLine1>880 Main Street</AddressLine1>

  <AddressLine2>80581</AddressLine2>

  <City>Waltham</City>

  <State>MA</State>

  <ZipCode>02451</ZipCode>

  <AssociatesInfo>

  <AssociateInfo>

  <FRST_NM>ANA I</FRST_NM>

  <LST_NM>Kary</LST_NM>

  </AssociateInfo>

  <AssociateInfo>

  <FRST_NM>ANA II</FRST_NM>

  <LST_NM>Lou</LST_NM>

  </AssociateInfo>

  </AssociatesInfo>

  </BankingCenter>

  <BankingCenter>

  <BankingCenterID>14210</BankingCenterID>

  <BankingCenterName>East End</BankingCenterName>

  <AddressLine1>4212 wood dr</AddressLine1>

  <AddressLine2></AddressLine2>

  <City>lakemary</City>

  <State>FL</State>

  <ZipCode>32709</ZipCode>

  <AssociatesInfo>

  <AssociateInfo>

  <FRST_NM>Andy</FRST_NM>

  <LST_NM>Miller</LST_NM>

  </AssociateInfo>

  <AssociateInfo>

  <FRST_NM>Joe</FRST_NM>

  <LST_NM>Robert</LST_NM>

  </AssociateInfo>

  </AssociatesInfo>

  </BankingCenter>

  </BankingCenters>

  thanks

  Arun

• PSB

  SSChampion

  Points: 13195

  More actions

  December 8, 2010 at 11:31 am

  #1260242

  If you use a XML Sourec editor , you will notice there is a output name drop down in the editor. There you will notice Banking center and AssociateInfo as the dropdowns after you have correctly defined connections for the XML editor. Also the xml editor will generate a schema for you at a location you desire.Select Banking center first and then select the columns that you want to be populated. Use data conversions if required and then connect to a sql server table for banking using ole db destination.

  In the same data flow tab use another XML source editor but now select associate info.Select the fields that you want to be populated.Create the table in sql and connect the xml editor to a ole db destination .

• arun_kumar14

  SSC Enthusiast

  Points: 109

  More actions

  December 8, 2010 at 2:17 pm

  #1260317

  but I do have to maintain the relation here. banking center ID to AssociateInfo.

  ex: for banking center ID:14213, I have two records of Associates Info

  <AssociatesInfo>

  <AssociateInfo>

  <FRST_NM>ANA I</FRST_NM>

  <LST_NM>Kary</LST_NM>

  </AssociateInfo>

  <AssociateInfo>

  <FRST_NM>ANA II</FRST_NM>

  <LST_NM>Lou</LST_NM>

  </AssociateInfo>

  </AssociatesInfo>

  I dont think if I go for two xml source in data flow task, I could achieve this?