1

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.

Community
  • 1
  • 1
user1757018
  • 35
  • 2
  • 7

1 Answers1

3

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.

Control flow

  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

dataflow

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thanks for a quick reply.I checked all the four properties for A) 1.The Sproc exists 2.The account has access,I am using the same account I am accessing SSMS and it works over there,when I parse the query it says parsed. 3. Yes ,It was created under a different Schema.I dropped it and created a new one with dbo schema,yet I get the same error,this time I also inluded EXEc .dbo.USP_PMAXML 4.As mentioned in post I referenced I used ado.net and result set as none but the connection string refers to corrrect connection. – user1757018 Oct 18 '12 at 18:46
  • @ billinkc: I tried second process but could not create STEP 4.In a derived column transformation,I tried creating a derived column and name it as FIleName. Also tried to "C:\ssisdata\so_xmlExtract.xml" in the expression window but it gives error when I click OK – user1757018 Oct 18 '12 at 19:06
  • I have added pictures as well as addressed the issue you are encountering with the value you put in the derived column – billinkc Oct 18 '12 at 20:50
  • @bilinkc : Thanks a lot.It is working and creating a XML which has data but all the data is coming in a single row.In ssms when I click on the result it gives me a structured XML.Is this behaviour expected or are there any tweaks to it to get the structure right.? Also I cannot open the file as XML Editor.It gives me error.It can be opened with Notepad! – user1757018 Oct 18 '12 at 23:10