SSIS - Can't Read XML - The file is being used by another process

Question

I am not able to read an XML file using SSIS. My goal is to read it and then insert the data into a MSSQL database (it would be a lookup table)

This is my data flow so far

where the process "Leer Usuarios" (Read Users in english) is the one which is in charge of reading the xml. An example of the xml file is

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<NewsItemsTable>
    <NewsItemRow>
        <ID>1</ID>
        <Edad>28</Edad>
        <Estado_Civil>Soltero</Estado_Civil>
        <Sexo>F</Sexo>
        <Provincia>Ciudad Autonoma Buenos Aires</Provincia>
        <Localidad>La Paternal</Localidad>
    </NewsItemRow>
    <NewsItemRow>
        <ID>2</ID>
        <Edad>28</Edad>
        <Estado_Civil>Soltero</Estado_Civil>
        <Sexo>F</Sexo>
        <Provincia>Ciudad Autonoma Buenos Aires</Provincia>
        <Localidad>La Boca</Localidad>
    </NewsItemRow>
</NewsItemsTable>

and the XSD (generated by SSIS) is

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="NewsItemsTable">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="NewsItemRow">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="ID" type="xs:integer" />
              <xs:element minOccurs="0" name="Edad" type="xs:short" />
              <xs:element minOccurs="0" name="Estado_Civil" type="xs:string" />
              <xs:element minOccurs="0" name="Sexo" type="xs:string" />
              <xs:element minOccurs="0" name="Provincia" type="xs:string" />
              <xs:element minOccurs="0" name="Localidad" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

When I run this dataflow, I get the error on the XML Source that the xml file is being used in another process. I don't have any other program using it, I have even restarted the machine (MS Server 2003) and I still have the same issue.

and the error message in progress tab

The translation for the error would be

The process could not access the file because it is being used by another process

What could be causing this problem ? How can I fix it ? I am pretty new to SSIS, so if you need more info I can provide it.

Thanks in Advance

UPDATE What I've noticed (because I've a backup of the xml) is when I read the XML for the first time with the ETL it inserts a lot of dummy data as forms of nulls.

Those nulls weren't there before

What could be causing this issue ? Might this be related with the original problem ?

Try renaming the XML file in windows explorer to double check that it is in use. Then download process explorer (http://serverfault.com/questions/1966/how-do-you-find-what-process-is-holding-a-file-open-in-windows) and work out what has the file open. Is that your entire package? — Nick.Mc, Oct 25 '15 at 00:14
@Nick.McDermaid yes I am able to rename the XML file. I have other packages but none of the other tasks is reading any xml. — Gonzalo.-, Oct 25 '15 at 19:47

Gonzalo.- · Accepted Answer · 2015-10-25T22:37:59.813

2

I found the issue

The logging was enabled and the log entries were written into the same file I was trying to read. I misunderstood the configuration of logging, and I had put the wrong file. Now I picked a new xml fresh and it is working correctly.

edited Oct 25 '15 at 22:37

answered Oct 25 '15 at 20:19

Gonzalo.-

12,512
5
50
82

2

Thanks for returning and posting your solution! – Nick.Mc Oct 25 '15 at 22:30

SSIS - Can't Read XML - The file is being used by another process

1 Answers1