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?
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