使用 SSIS 从存储过程导出 XML 文件 [英] Exporting XML file from a Stored Procedure using SSIS

问题描述

有一个存储过程，它有 XML 作为输出，它在 SSMS 中显示 xml 输出.我应该如何使用 SSIS 从此存储过程生成到特定位置的 XML 文件.我找到了一个链接，建议使用 VB 和脚本任务来做到这一点.不使用 Script Task 可以做到这一点吗?我也尝试在此链接中执行以下步骤:

和使用 SSIS 提取 XML表数据到文件的表示

have a Stored Procedure which has XML as Output and it shows xml output in SSMS. How should I generate an XML file to a specific location from this Stored Procedure using SSIS. I found a link which suggests using VB and script task to do this.Can this be done without using Script Task ? Also I tried following steps in this link :

How to export from SQL Server to XML

The Package fails at Execute SQL Task itself and gives the error [Execute SQL Task] Error: Executing the query "EXEC USP_PMAXML" failed with the following error: "Could not find stored procedure 'EXEC USP_PMAXML'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks for your time and help.

解决方案

A) The error you are receiving indicates it cannot find the stored procedure. You will want to verify

1. the stored procedure exists
2. The account access the stored procedure has execute permissions for it
3. The stored procedure exists in the default schema, probably dbo, for the account. {I've seen issues where procedures are created under user schemas [domain\user].USP_PMAXML }
4. The Connection Manager you are using is pointing the correct server and catalog (database).

B) If you would like to use an out of the box approach and avoid scripting, then remove your Execute SQL Task. Below I show an Execute SQL Task for reproduction purposes. It creates a stored proc that generates XML.

1. Add a Data Flow Task.
2. Within the Data Flow Task, add an OLE DB Source.
3. Configure your OLE DB Source to use the connection manager and the stored procedure we verified is correct from step 1

4. Assuming there should only be one file generated, add a Derived Column Transformation out of the OLE DB Source and inside of it, define the output file name which I assume is C:\ssisdata\so_xmlExtract.xml. I will further assume you rename the column as FileName. The exact value you would use is "C:\ssisdata\so_xmlExtract.xml" Note the doubling of slashes as we must escape the \ character as well as wrap with double quotes.

5. At this point, you're ready to use the Export Column Transformation. Examples Export Varbinary(max) column with ssis and Using SSIS to extract a XML representation of table data to a file

这篇关于使用 SSIS 从存储过程导出 XML 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！