我有一个列类型为 xml 的表。我还有一个目录，可以有 0 到 n 个 xml 文档。对于每个 xml 文档，我需要在表中插入一个新行并将 xml 放入 xml 列中。
为了满足客户的需求，我需要使用 SSIS 包来执行此操作。我计划使用存储过程插入 xml，传入文件路径。
我已经创建了存储过程并进行了测试，它按预期运行。
我的问题是，如何从 SSIS 包中为每个 xml 文档（特定目录）执行存储过程？
预先感谢您的任何帮助。
-
I have a table with a column type of xml. I also have a directory that can have 0 to n number of xml documents. For each xml document, i need to insert a new row in the table and throw the xml into the xml column.
To fit with our clients needs, I need to perform this operation using an SSIS package. I plan to use a Stored Procedure to insert the xml, passing in the file path.
I've created the stored procedure and tested, it functions as expected.
My question is, how do I execute the stored procedure from an SSIS package for each xml document is a specific directory?
Thanks in advance for any help.
-
发布评论
评论（2）
基本上，您只需要循环遍历文件并获取完整的文件路径以传递给存储过程。使用 For Each 循环和 ForEach 文件枚举器可以轻松完成此操作。此页面对如何设置有很好的描述：
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
在循环内，您只需访问每次循环执行时填充的变量（XML 文件是找到）并将其作为参数发送到执行 SQL 任务（驻留在 For Eacu 循环容器内）以调用存储过程。以下是将变量作为参数传递的示例：
http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
Basically you just need to loop through the files and get the full file paths to pass to the stored proc. This can be done easily using a For Each Loop and the ForEach File Enumerator. This page has a good description of how to set that up:
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Within the loop then you just access the variable that is populated each time the loop executes (an XML file is found) and send it as a parameter into an Execute SQL Task (residing inside your For Eacu Loop container) to call your stored procedure. Here is an example of passing variables as parameters:
http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
您不需要为此使用存储过程。您可以在 SSIS 包中完成所有这些操作。操作方法如下：
让 For-Each 循环任务读取文件夹中的所有可用文件。将文件的完整路径放入名为 XMLFileName 的变量中。
在 For-Each 循环内，使用数据流任务读取内容。
OLE_SRC 正在从同一 SQL Server 读取数据，其语句为
SELECT GetDate() as CurrentDateTimeDerivedColumn 组件使用 XML 文件的完整路径创建一个名为 XMLFilePath 的列。
ImportColumn 组件执行以下操作：魔法。它将把 XMLFilePath 作为输入列，为其提供您创建的新输出列的 LineageId，然后它将为您导入完整的 XML。了解有关如何设置的更多信息：
http://www .bimonkey.com/2009/09/the-import-column-transformation/
使用 OleDB 目标写入表。
You don't need to use a stored procedure for this. You can do all of this within an SSIS package. Here's how:
Have a For-Each Loop task read all available files in the folder. Put the full path of the file into a variable called XMLFileName
Inside the For-Each loop, use a Data-Flow task read the contents.
The OLE_SRC is reading from the same SQL Server and it's statement is
SELECT GetDate() as CurrentDateTimeThe DerivedColumn component creates a column called XMLFilePath with the full path of the XML file
The ImportColumn component is the one that does the magic. It will take the XMLFilePath as an input column, give it the LineageId of a new output column you create and it will import the full XML for you. Read more on how to set it up here:
http://www.bimonkey.com/2009/09/the-import-column-transformation/
Use the OleDB Destination to write to the table.