Using .xml files in a project

Question

Using .xml files in a project

Oscar_Boots

Old Hand

Points: 362
More actions
July 3, 2019 at 5:52 am

#3659623

Hi,
I have three input files for my SSIS project, 1. .txt, 2. .xml, 3. .xls.
I've been able to bring the .txt and .xls data into the project but I'm having some trouble with the .xml file.
To test that the data has come into the project, I've tried to export it but I get .xml type text in the first field of the .xml data table.
My question is; Can someone show me how to open my .xml data (which node) that I've imported in a component so I can see it has successfully imported?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

XML files come in different formats. Is your question about a particular format, or about how to use the XML source, or something else?

... so I can see it has successfully imported?

To test whether an import has been successful, don't you just do a SELECT from the target table and look for the imported data? If it's not there, or in the wrong format ... unsuccessful...

It's difficult to know what you are looking for here.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Oscar_Boots Old Hand Points: 362 More actions · Answer 2

Thanks Phil for your feedback,

I've created an .xml file using the CustomerSales table from the AdventureWorks sample db.

I've posted a sample of this data below;

These are the steps I'm taking to bring this data into my project - note I need to use the Script Component to add the .xml file.

After adding a Data Flow to the Control flow tab, I add the Script Component in the Data Flow tab area and create a Connection Manager that connects to the .xml file.

Next I add the field names and datatypes to the 'Inputs & Outputs' to match the headings in the CustomerSales table.

I'm not sure whether I should add the full .xml script to the 'Script' area of the Script Component as I'm not sure where to add it.

When I add an Output to see the data, all I am seeing are the headings.

Is this due to the script not being added to the Script Component or is there another reason?

Thanks

<?xml version="1.0" encoding="utf-8"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="urn:schemas-microsoft-com:office:excel2" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
 <x:ExcelWorkbook>
  <x:ActiveSheet>0</x:ActiveSheet>
 </x:ExcelWorkbook>
 <o:DocumentProperties>
  <o:Author>Laptop</o:Author>
  <o:Created>2019-04-22T01:25:09Z</o:Created>
  <o:LastSaved>2019-07-02T04:13:16Z</o:LastSaved>
 </o:DocumentProperties>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <ss:Alignment ss:Vertical="Bottom" />
   <ss:Font ss:Color="#000000" ss:FontName="Calibri" ss:Size="11" />
  </Style>
  <Style ss:ID="S21">
   <ss:Alignment ss:Vertical="Bottom" />
   <ss:Font ss:Color="#000000" ss:FontName="Calibri" ss:Size="11" />
   <ss:NumberFormat ss:Format="0.00E+00" />
  </Style>
  <Style ss:ID="S22">
   <ss:Alignment ss:Vertical="Bottom" />
   <ss:Font ss:Color="#000000" ss:FontName="Calibri" ss:Size="11" />
   <ss:NumberFormat ss:Format="M/d/yyyy h:mm" />
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <ss:Names />
  <ss:Table ss:DefaultRowHeight="15" ss:DefaultColumnWidth="48" ss:ExpandedRowCount="19821" ss:ExpandedColumnCount="7">
   <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="90" />
   <Column ss:Index="7" ss:StyleID="S22" ss:AutoFitWidth="0" ss:Width="120" />
   <Row ss:Index="1">
    <Cell><Data ss:Type="String">CustomerID</Data>
    </Cell>
    <Cell><Data ss:Type="String">PersonID</Data>
    </Cell>
    <Cell><Data ss:Type="String">StoreID</Data>
    </Cell>
    <Cell><Data ss:Type="String">TerritoryID</Data>
    </Cell>
    <Cell><Data ss:Type="String">AccountNumber</Data>
    </Cell>
    <Cell><Data ss:Type="String">rowguid</Data>
    </Cell>
    <Cell><Data ss:Type="String">ModifiedDate</Data>
    </Cell>
   </Row>
   <Row ss:Index="2">
    <Cell><Data ss:Type="Number">1</Data>   ...

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

If you are importing an Excel file, why not do it like this?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Oscar_Boots Old Hand Points: 362 More actions · Answer 4

Thanks but I'm not importing an Excel, it's an .xml file as shown above.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 5

Excel files are (compressed) XML files.

And the references to Excel.Sheet, ExcelWorkbook and the like are rather compelling. Are you certain about this?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel