Exporting XML file from a Stored Procedure using S

问题:

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.

回答1:

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