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

Problem : When we have Multiple Namespaces in the Xml Source which we need to load the Data from  as we shown below

Then we will face a issue in generating XSD for that XML

Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

Solution :

First we need to avoid multiple namespaces to transform your source file to a format that doesn’t refer to the namespaces. SSIS has an XML task that can do the transformation.

1) Add the XML task to an SSIS  Control Flow and edit it.

2) Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem.

3) Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file ( ModifiedInput.XML).

4) Add the below script to a new file and save it with an xslt file extension (Input.XSLT)

<?xml version="1.0" encoding="utf-8" ?> 
<xsl:stylesheet version="1.0"         xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
  <xsl:output method="xml" indent="no" /> 
  <xsl:template match="/|comment()|processing-instruction()"> 
    <xsl:copy> 
      <xsl:apply-templates /> 
    </xsl:copy> 
  </xsl:template> 
  <xsl:template match="*"> 
    <xsl:element name="{local-name()}"> 
      <xsl:apply-templates select="@*|node()" /> 
    </xsl:element> 
  </xsl:template> 
  <xsl:template match="@*"> 
    <xsl:attribute name="{local-name()}"> 
      <xsl:value-of select="." /> 
    </xsl:attribute> 
  </xsl:template> 
</xsl:stylesheet> 

This is how the final XML Task Editor looks like

5) When you run the XML task, it will take your original file and apply the transformation rules defined in the XSLT file. The results will be saved in your new XML file. This task only needs to be run once for the original XML file. When you look at the new file, you’ll see the same data as in the original, but without namespace references.

6) now connect the new modified Xml then are able to create the XSD without any issue