SSIS: Create CSV File From XML Source Using Variable data

Question

In SSIS, am attempting to use data from a web service to create a csv file.

In my Control Flow, I have created a Web Service Task that saves its output to a User Variable.

When the Web Service Task finishes, it starts a Data Flow Task.

The Data Flow Task has a single XML Source, with the Data access mode set to "XML data from variable" pointing to the variable that (supposedly) has the XML data from the web service.

Setting a breakpoint both before and after the DFT prove that that variable is being set to the XML text that I am expecting.

For example, the XML looks something like this (names changed to protect proprietary info):

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfMyItemObject xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <MyItemObject>
    <OrderID xmlns="http://webservices.mycompany.com/MyPath/">111111</OrderID>
    <ItemID xmlns="http://webservices.mycompany.com/MyPath/">123456</ItemID>
  </MyItemObject>
  <MyItemObject>
    <OrderID xmlns="http://webservices.mycompany.com/MyPath/">222222</OrderID>
    <ItemID xmlns="http://webservices.mycompany.com/MyPath/">678901</ItemID>
  </MyItemObject>
  <MyItemObject>
    <OrderID xmlns="http://webservices.mycompany.com/MyPath/">333333</OrderID>
    <ItemID xmlns="http://webservices.mycompany.com/MyPath/">234567</ItemID>
  </MyItemObject>
  <MyItemObject>
    <OrderID xmlns="http://webservices.mycompany.com/MyPath/">444444</OrderID>
    <ItemID xmlns="http://webservices.mycompany.com/MyPath/">890123</ItemID>
  </MyItemObject>
</ArrayOfMyItemObject>

That data flow task simply processes into a Flat File Destination (the csv file). The columns in the flat file are mapped to the values in the XML.

However, when I run this, I only get the column names, and no data.

I have verified that the Web Service task returns what I am expecting by running another Web Service task using the same web service, but putting that output directly into a file.

Answer 1

Here is an answer that says what I've done to work around this issue. If somebody can answer better with a "why" I will mark that as the answer.

The problem was with including the namespace with the XML from the web service, combined with using "targetNamespace" in the xsd.

Originally, the xsd looked something like this:

<?xml version="1.0"?>
<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            attributeFormDefault="unqualified"
            elementFormDefault="qualified"
            targetNamespace="http://webservices.mycompany.com/MyPath/">
    <!-- reset of xsd here -->
</xsd:schema>

I needed to remove the targetNamespace attribute.

<?xml version="1.0"?>
<xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            attributeFormDefault="unqualified"
            elementFormDefault="qualified"
    <!-- reset of xsd here -->
</xsd:schema>

Then, I needed to remove references to that namespace in the XML returned from the web service. To do that, I created a Script task right after the Web Service Task. This task has a ReadWriteVariable defined for the variable that holds the XML text, which will get passed the XML Source in the Data Flow Task.

The code of the script simply removes the namespace from the XML and returns it back:

public void Main()
{
    string xml = Dts.Variables["User::WebServicesOutput"].Value.ToString();
    xml = xml.Replace(" xmlns=\"http://webservices.mycompany.com/MyPath/\"", "");
    Dts.Variables["User::WebServicesOutput"].Value = xml;

    Dts.TaskResult = (int)ScriptResults.Success;
}

(I realize that I code do it in one line, but this is cleaner, and allows me to further modify the XML if I'd like to in the future).

By doing this, the XML Source correctly processes the XML passed in the variable.