SSIS 2012 - загрузка XML-файлов из папки в таблицу COLUMN SQL Server с использованием задачи "Выполнение SQL" и контейнера ForEachLoop

Question

Сегодня у меня новая ситуация, когда наш клиент спрашивает следующее:

У нас есть XML-файлы в папке. Нам нужно загрузить эти файлы в столбец таблицы SQL Server (который имеет тип данных XML); мы НЕ загружаем выходные данные XML в различные таблицы SQL Server, а загружаем сам файл XML в столбец с типом данных XML в базе данных SQL Server.

Это должно быть сделано только в службах SSIS в соответствии с требованиями клиента. Поэтому я использую задачу "Выполнение SQL" таким образом. (Помимо файла XML нам понадобится ImportDate, FileName, так далее.)

Тип подключения OLE DB; У меня есть SQL-запрос, как показано ниже:

INSERT INTO dbo.tablename (IncomingXMLfile, ImportDate)
-- I am using just 2 columns as an example for this table--
SELECT  CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Mic\...........\API_Schemas\ABC.xml', SINGLE_BLOB) AS x;

Это прекрасно работает, когда я жестко закодировал только один файл. Теперь в C:\Mic\...\API_Schemas папки, есть много файлов XML, мне нужно загрузить каждый из них, и кроме этого получить их ImportDate (который GETDATE()) и имя самого файла (я не упомянул этот столбец в инструкции INSERT). Я должен использовать контейнер ForEachLoop и поместить задачу "Выполнение SQL" в этот контейнер.

Следовательно, есть 2 аспекта этой проблемы:

1. параметризация имени файла внутри задачи "Выполнение SQL".

2. используя контейнер ForEachLoop (и помещая эту задачу "Выполнение SQL" внутри) для запуска каждого XML-файла в папке.

Я использую пользовательскую переменную под названием Filename (тип данных строки), который имеет значение ABC.xml (первый файл из серии файлов XML в папке); Я параметризирую вышеупомянутый запрос TSQL следующим образом (см. Ниже):

INSERT INTO dbo.tablename (IncomingXMLfile, ImportDate)
SELECT  CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Mic\...........\API_Schemas\?', SINGLE_BLOB) AS x;

Я сопоставляю этот параметр (?) к пользовательской переменной Filename в этой задаче "Выполнение SQL" (ParameterMapping вкладка). Правильно обработанный запрос в задаче "Выполнение SQL"! Однако, когда я выполняю эту задачу SQL, я получаю эту ошибку:

Msgstr "Имя параметра не распознано." Возможные причины сбоя: проблемы с запросом, свойство ResultSet установлено неправильно, параметры установлены неправильно или соединение установлено неправильно.

Эта ситуация довольно сильно отличается, когда я загружаю данные из файлов XML (используя редактор исходного кода XML, с режимом доступа к данным (данные XML из переменной) и т. Д.) В таблицы SQL Server. В нашем случае мы загружаем сам файл XML в таблицу COLUMN SQL Server, которая имеет тип данных XML, и получаем информацию о различных файлах XML, загруженных из папки. В результате я не уверен, как использовать переменную для запуска этого пакета.

• Может ли кто-нибудь прежде всего помочь мне в том, как правильно параметризировать имя файла?

• И позже, как настроить контейнер ForEachLoop для чтения каждого файла из папки?

Я запутался в том, как использовать пользовательские переменные в этом сценарии.

Answer 1

Сначала я создаю таблицу для хранения XML:

CREATE TABLE XMLstore (
    IncomingXMLfile xml,
    ImportDate datetime
)

Создайте 2 файла для теста ABC.xml а также DEF.xml и вставьте в них некоторый контент XML:

<some>
    <row id="1">
        <stuff>1</stuff>
    </row>
</some>

Затем запустите этот скрипт:

DECLARE @command varchar(1000),
        @dir varchar(max) = 'D:\API_Schemas\',
        @n int = 0,
        @i int = 1,
        @sql nvarchar(max)

DECLARE @files TABLE (
    id INT IDENTITY(1,1),
    files varchar(1000)
)

SET @command = 'dir "'+ @dir +'" /B'

INSERT INTO @files (files)
EXEC xp_cmdshell @command

SELECT @n = COUNT(*) 
FROM @files
WHERE files like '%.xml%'

WHILE @n >= @i
BEGIN

    SELECT @sql = N'
    INSERT INTO XMLstore (IncomingXMLfile, ImportDate)
    SELECT  CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
    FROM OPENROWSET(BULK '''+@dir+files+''', SINGLE_BLOB) AS x;'
    FROM @files
    WHERE id = @i

    EXEC sp_executesql @sql

    SET @i = @i + 1
END

После этого я выбираю из таблицы XMLstore:

SELECT *
FROM XMLstore

И получить вывод:

IncomingXMLfile                                 ImportDate
<some><row id="1"><stuff>1</stuff></row></some> 2016-10-06 10:17:41.453
<some><row id="2"><stuff>2</stuff></row></some> 2016-10-06 10:17:41.457

Описание:

Файлы хранятся здесь D:\API_Schemas\, я использую xp_cmdshell запустить команду dir "D:\API_Schemas\" /B чтобы получить все файлы из этого каталога.

/B используется для включения

Используется пустой формат (без информации о заголовке или резюме)

поэтому мы получаем только имена файлов. И положить их в @files Таблица.

Эта таблица получила столбец идентификации, который добавляет id началось с 1 к каждой строке (файлу). Таким образом, мы можем повторить цикл throw while, используя простой счетчик (@i).

В цикле while мы создаем динамический запрос SQL и запускаем его.

Замечания:

Вместо xp_cmdshell ты можешь использовать xp_dirtree (без документов и без поддержки), например:

DECLARE @dir varchar(100) = 'D:\API_Schemas\'

DECLARE @files TABLE (
    id INT IDENTITY(1,1),
    files varchar(1000),
    depth int,
    [file]  int
)

INSERT INTO @files 
EXEC xp_dirtree @dir, 1, 1