sql server - insert XML file in SQL via SSIS

Question

I have this procedure

ALTER proc [dbo].[adenti] 
(
   @entra nvarchar(max)
)
as 
DECLARE @sql varchar(4000); 
SET @sql = 'INSERT INTO provaxml (arquivo) SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(BULK ''' + @entra + ''', SINGLE_BLOB) as arquivo'; 
EXEC( @sql );

The code above works.

Is it possible to use Integration Services to insert all the XML file in a path into a SQL table?

Answer 1

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.

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).

Foreach Loop Container

Configure as such

Collection

Variable Mappings

Execute SQL Task

Configure thusly

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

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

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

Make note of the ID here

Tie that ID back to the column with the name

OLE DB Destination

Configure. Does not support Fast Load option.

Reference

Nice post on using the Import Column Transformation