Use CRM Dynamics Online odata endpoints in SSIS

In part 1 ¹ we retrieved the XML response of CRM Online odata without user interaction. Time that SSIS handle this XML.

Making SSIS reading CRM XML Output

Before spending effort on creating an SSIS Script component for authenticating & getting the output, let's check how SSIS understand the XML file we have.

Open SSIS
Create a data flow
In Other source, insert an XML Source
if you click on Columns in order to have a look, you fail

XML Schema (XSD) is not specified. Select an existing XSD or click Generate XSD to create an XSD from the XML file

You click on Generate XSD, saving the schema anywhere and you got a fatal error :(

Unable to infer the XSD from the XML file. The XML contains multiple namespaces.

indeed that is true

<feed xml:base="https://XXXXX.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">  
  <title type="text">be_allianceSet</title>
  <id>https://XXXXXX.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc/be_allianceSet/</id>
  <updated>2016-03-16T08:34:23Z</updated>
  <link rel="self" title="be_allianceSet" href="be_allianceSet" />

We all know now that next hours will be painful with XML Transformation as SSIS can do that for us. But are we skilled enough us ? (not sure). Hopefully google is here.
Finding an xsl file removing all namespace is quite easy, and we take the opportunity to reduce the output size by removing all link nodes and remove spaces

<?xml version="1.0"?>  
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  
    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="*[local-name() = 'link']" />

    <!-- template to copy attributes -->
    <xsl:template match="@*">
        <xsl:attribute name="{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>

    <xsl:template match="*/text()[normalize-space()]">
    <xsl:value-of select="normalize-space()"/>
</xsl:template>

<xsl:template match="*/text()[not(normalize-space())]" />  
</xsl:stylesheet>

Create an XML Task for transforming the input XML with your xsl file

and plug it to data flow task with an XML source, this time click on generate XSD and it should succeed
when you click on Columns, you get few warning about default string length and ... and much too many Output name ...

and the node we are interested in, properties only contains few fields :( :( :(

it seems that all nested information, such any CRM EntityReference or OptionSetValue are recognize as different entities and should be "flatten" exposing only a specific subnode value. Second hint that I've discovered later, XML attributes were not helping as well.
This time I needed help from a better XSLT person (let's say anybody) on stackoverflow ² whom greatly helped me to get a nice xsl file

<?xml version="1.0"?>  
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  
    <xsl:output indent="yes" method="xml" encoding="utf-8" omit-xml-declaration="yes"/>

    <xsl:template match="*">
        <xsl:element name="{local-name()}">
            <xsl:apply-templates select="@* | node()"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="*[local-name() = 'link']" />

    <xsl:template match="*[@*[local-name() = 'type']='Microsoft.Crm.Sdk.Data.Services.EntityReference']">
        <xsl:element name="{local-name()}">
            <xsl:value-of select="*[local-name() = 'Id']" />
        </xsl:element>
    </xsl:template>

    <xsl:template match="*[@*[local-name() = 'type']='Microsoft.Crm.Sdk.Data.Services.OptionSetValue']">
        <xsl:element name="{local-name()}">
            <xsl:value-of select="*[local-name() = 'Value']" />
        </xsl:element>
    </xsl:template>

    <!-- template to copy attributes -->
    <xsl:template match="@*" />

    <xsl:template match="*/text()[normalize-space()]">
    <xsl:value-of select="normalize-space()"/>
</xsl:template>

<xsl:template match="*/text()[not(normalize-space())]" />  
</xsl:stylesheet>

and finally, the holy grail

soon to be written third part will provide a scenario related to paging : odata only return 50 records at a time.