Insert Xml File in SQL via Ssis

insert XML file in SQL via SSIS

There are two different approaches you can do.

The first is as user569711 outlined and use a ForEach Enumerator and call your existing stored procedure. Advantage to this is your behaviour should be exactly as what you are currently experiencing and your testing should only need to focus on ensuring the SSIS package is picking up the right files.

The second is to use the out of the box capabilities of SSIS to deal with importing BLOB types.

Control Flow

You will want 1 to 2 variables defined depending upon your approach. Both will be string data types. I created SourceFolder and CurrentFileName. The former defines where the files will come from and is used in either approach. The latter is used in the ForEach Loop Container to capture the "current" file.

control flow

Data Flow

To make the data flow work, you will need to get the fully qualified list of file names added into the pipeline. Easiest way is to use a Script Transformation, acting as a source and have that add in all the files meeting your condition (*.xml).

data flow

Foreach Loop Container

Configure as such

Collection

Sample Image

Variable Mappings

Sample Image

Execute SQL Task

Configure thusly

Sample Image

Script Source

This task will add the available files into the data flow. Minor note, this will traverse subfolders which differs from how we have the Foreach configured. It's a simple change to the third parameter (or omission) to make it top level only.

Identify your variable so it is available in the script task

Sample Image

Add the appropriate output columns. Your lengths may vary based on your environment.

Sample Image

Script here

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void CreateNewOutputRows()
    {
        string fileMask = string.Empty;
        string sourceFolder = string.Empty;

        fileMask = @"*.xml";
        sourceFolder = this.Variables.SourceFolder;

        foreach (string fileName in Directory.GetFiles(sourceFolder, fileMask, SearchOption.AllDirectories))
        {
            Output0Buffer.AddRow();
            Output0Buffer.FileName = fileName;
            Output0Buffer.SourceName = "Dataflow";
        }
    }
}

Import Column Transformation

Configure like this

Sample Image

Make note of the ID here

Sample Image

Tie that ID back to the column with the name

Sample Image

OLE DB Destination

Configure. Does not support Fast Load option.

Sample Image

Reference

Nice post on using the Import Column Transformation

Import XML files with different structures using SSIS

So what I did as a solution to this particular case was:
1- add a C# script that reads the xml file and keep the common nodes only and save their values to my dts variables.
2- insert into my SQL Server table the variables I just populated.
Both tasks were in a for each loop to go through all the xml files in a specific directory.
Hope it helps!

Is it possible to push XML file(Load the Data) to SQL Server via SSIS (Visual Studio) without XSD file?

In SQL Server you can convert a text representing XML as you please, like:

CONVERT(XML, N'somexml', 2)

In the code above replace somexml with your XML. The docs provide a nice example about how you can specify XML as a type of a column:

CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>');

Before you continue reading, try experimenting by the creation of the table above and inserting XML into it, as:

insert into T(xml)
values(N'<element1/><element2/>');

Now, if your tests confirm that you are able to convert a text into XML in the way shown above, then the only thing remaining is to convert whatever XML you have in your application code into a text. I do not know what your application code/framework is, but this is how you can do it with C#:

https://forums.asp.net/t/1630414.aspx?How+to+convert+XML+to+Text+file+using+C+

How to load an XML file into a database using an SSIS package?

Here is a possible option which demonstrates how to load multiple XML files having same definition into an SQL Server table. The example uses SQL Server 2008 R2 and SSIS 2008 R2. The example shown here loads three XML files into an SQL table using SSIS Data Flow Task with the help of XML Source component.

Step-by-step process:

Create a table named dbo.Items using the script given under SQL Scripts section.
Create an XSD file named Items.xsd in the folder path C:\temp\xsd using the content provided under XSD File section.
Create three XML files namely Items_1.xml, Items_2.xml and Items_3.xml in the folder path C:\temp\xml using the content provided under XML Files section.
On the package, create 3 variables namely FileExtension, FilePath and FolderPath as shown in screenshot #1.
On the package's Connection Managers, create an OLE DB Connection named SQLServer to connect to the SQL Server Instance as shown in screenshot #2.
On the Control Flow tab, place a Foreach loop container and a Data Flow Task within the Foreach loop container as shown in screenshot #3.
Configure the Foreach Loop container as shown in screenshots #4 and #5.
Double-click on the Data Flow Task to navigate to the Data Flow tab. Place an XML Source component and an OLE DB Destination as shown in screenshot #6.
Configure the XML Source as shown in screenshot #7 and #8. The XML file path will be retrieved from the variable FilePath. This variable will be populated by the Foreach Loop container.
Configure the OLE DB Destination as shown in screenshots #9 and #10.
Screenshots #11 and #12 show the package execution.
Screenshot #13 shows the table data before the package execution. Screenshot #14 shows the table data after the package execution. The data in the table dbo.Items now contains the data present in three XML files.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](50) NOT NULL,
    [ItemName] [nvarchar](60) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

XSD File

<xsd:schema xmlns:schema="ItemsXSDSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ItemsXSDSchema" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Items">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Item">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="Id" type="sqltypes:int" />
                            <xsd:element name="ItemNumber">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="20" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="ItemName">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                                        <xsd:maxLength value="60" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                            <xsd:element name="Price">
                                <xsd:simpleType>
                                    <xsd:restriction base="sqltypes:numeric">
                                        <xsd:totalDigits value="18" />
                                        <xsd:fractionDigits value="2" />
                                    </xsd:restriction>
                                </xsd:simpleType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

XML Files

Items_1.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>I2345343</ItemNumber>
        <ItemName>Monitor</ItemName>
        <Price>299.99</Price>
    </Item>
</Items>

Items_2.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>J1231231</ItemNumber>
        <ItemName>Mouse</ItemName>
        <Price>29.99</Price>
    </Item>
</Items>

Items_3.xml

<?xml version="1.0"?>
<Items xmlns="ItemsXSDSchema">  
    <Item>
        <Id>1</Id>
        <ItemNumber>K0456212</ItemNumber>
        <ItemName>Keyboard</ItemName>
        <Price>49.99</Price>
    </Item>
</Items>

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

SSIS How to import xml elements into table

I am new in SSIS, but in internet found solution (maybe not the best but worked).

So here it comes.

First i create same table as you provided ParentTable, just changed desc to 255. Also added Connection Manager to package.
Created two new variables User::FileName = "some.xml" and User::SourceCatalog = "C:\xmlCatalog\"
Then added Data Flow Task in which I added Script Component (selected Source type).
Opened Script Transformation Editor in Script tab into ReadOnlyVariables property added newly created variables User::FileName,User::SourceCatalog.
In the tab Inputs and Outputs renamed Output 0 to XMLResultOutput and under Output Columns created two new columns xmlDesc (Data Type = Unicode string [DT_WSTR] 255) and xmlData (Data Type = Unicode string [DT_WSTR] 3000). This variables will be used later in C# script.

inputs and outputs

Pressed Edit Script... in the Script tab. In the opened window in the CreateNewOutputRows method paste this code:

XmlDocument xDoc = new XmlDocument();

string xml_filepath = Variables.SourceCatalog + Variables.FileName;
xDoc.Load(xml_filepath);

foreach (XmlNode xNode in xDoc.SelectNodes("//row"))
{

    this.XMLResultOutputBuffer.AddRow();

    this.XMLResultOutputBuffer.xmlData = xNode.OuterXml.ToString();

    this.XMLResultOutputBuffer.xmlDesc = xNode.SelectSingleNode("./desc").InnerText;//xNode.ChildNodes[0].InnerText;

}

Don't forget to add using System.Xml;

Added OLE DB Destination component, linked Script Component to it, selected table, mapped columns and THATS IT.

Importing and validating XML file using SSIS or just plain T-SQL?

Based on the requirements that you have mentioned, I would say that you can use best of both the worlds (T-SQL & SSIS).

I feel that T-SQL gives more flexibility in loading the XML data that you have described in the question.

There are lot of different ways you can achieve this. Here is one possible option:

Create a Stored Procedure that would take the path of the XML file as input parameter.
Perform your XML data load operation using the T-SQL way which you feel is easier.
Use SSIS package to perform error handling, file processing, archiving and send email.
Use logging feature available in SSIS. It just requires simple configuration. Here is a samples that show how to configure logging in SSIS How to track status of rows successfully processed or failed in SSIS data flow task?
A sample mock up of your flow would be as shown below in the screenshot. Loop the files using Foreach Loop container. Pass the file path as parameter to Execute SQL Task, which in turn would call the T-SQL that you had mentioned. After processing the file, using the File System Task to move the file to an archive folder.
Sample used in SSIS reading multiple xml files from folder
shows how to loop through files using Foreach loop container. It loops through xml files but uses Data Flow Task because the xml files are in simpler format.
Sample used in How to send the records from a table in an e-mail body using SSIS package? shows how to send e-mail using Send Mail Task.
Sample used in How do I move files to an archive folder after the files have been processed? shows how to move files to an Archive folder.
Sample used in Branching after a file system task in SSIS without failing the package shows how to continue package execution even after a particular task fails. This will help you to proceed with package execution even if Foreach Loop fails so you can send email. Blue arrow in the screenshot indicates on completion of previous task.
Sample used in How do I pick the most recently created folder using Foreach loop container in SSIS package? shows how to perform pattern matching.

Hope that gives you an idea.

Flow

Insert Xml File in SQL via Ssis