通过 SSIS 在 SQL 中插入 XML 文件 [英] insert XML file in SQL via SSIS

问题描述

我有这个程序

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

上面的代码有效.

是否可以使用 Integration Services 将路径中的所有 XML 文件插入到 SQL 表中?

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.

第一个是 user569711 概述并使用 ForEach Enumerator 并调用您现有的存储过程.这样做的好处是您的行为应该与您当前遇到的完全一样，您的测试只需关注确保 SSIS 包获取正确的文件.

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.

第二种是使用 SSIS 开箱即用的功能来处理导入 BLOB 类型.

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

您需要根据您的方法定义 1 到 2 个变量.两者都是字符串数据类型.我创建了 SourceFolder 和 CurrentFileName.前者定义了文件的来源，并在任一方法中使用.后者在 ForEach 循环容器中用于捕获当前"文件.

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.

要使数据流正常工作，您需要获取添加到管道中的文件名的完全限定列表.最简单的方法是使用脚本转换，作为源并将其添加到满足您条件的所有文件 (*.xml) 中.

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 的方式不同.这是对第三个参数(或省略)的简单更改，使其仅成为顶级.

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.

脚本在这里

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";
        }
    }
}

导入列转换

这样配置

在此处记下 ID

将该 ID 绑定回具有名称的列

Tie that ID back to the column with the name

配置.不支持快速加载选项.

Configure. Does not support Fast Load option.

关于使用导入列转换

这篇关于通过 SSIS 在 SQL 中插入 XML 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！