none
SSIS Data Flow of a SQL Server Stored Procedure producing XML to a file RRS feed

  • Question

  • So we are trying to follow this procedure...

    https://www.codeproject.com/Articles/635956/How-to-export-data-from-database-tables-to-an-XML

    In our Data Flow Task and OLE DB Source we have...

    EXECUTE [SchemaName].[SQLServerStoredProcedureName] ?,?

    WITH RESULT SETS
    (
    (
    [COl_XML] VARCHAR(MAX)
    )
    )

    Our SQL Server Stored Procedure utilizes a Start Date and an End Date Parameter that have been defined and provided and is set FOR XML PATH('Entity'), ROOT('Entity'), TYPE) AS COl_XML;

    We are using WITH RESULT SETS because there are numerous #Temporary Tables in our SQL Server Stored Procedure

    When we attempt to Execute, we are getting the following Error message...

    "EXECUTE statement failed because its WITH RESULT SETS clause specified type 'varchar(max)' for column #1 in result set #1, and the corresponding type sent at run time was 'xml'; there is no conversion between the two types.".

    Are we missing something here? We also tried WITH RESULT SETS [Col_XML] XML and that failed as well.

    We're pretty sure we have to use a Data Flow Task for this effort and cannot just do this from a Control Flow perspective.

    Thanks for your review and am hopeful for a reply.

    Friday, September 20, 2019 5:34 PM

All replies

  • Hi,
    The example has no mention of WITH RESULTSET.
    Make sure your code produces a valid XML, and maybe do not use the stored procedure.

    Start small and ensure it works in the most simple way.
    I would actually use a Script Task because it allows greater control over every aspect https://radacad.com/ssis-sql-server-to-xml-save-to-file


    Arthur

    MyBlog


    Twitter

    Friday, September 20, 2019 6:37 PM
    Moderator
  • Hi ITBobbyP,

    I advocated for a long time to create SSIS XML Destination Adapter with the SSIS engineering team starting with SSIS 2005. Unfortunately, it fell on the deaf ears.

    Personally, I started to use bcp.exe command line utility to generate XML data feeds from the SQL Server. It is possible to call a stored procedure or direct SQL, like below in the @SQL variable.

    /*
    -T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.
    -U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.
    */
    DECLARE @SQLCmd VARCHAR(8000)
       , @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'
       , @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'
       , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; SELECT TOP(1) TABLE_SCHEMA, TABLE_NAME FROM master.Information_Schema.Tables FOR XML PATH(''r''), TYPE, ROOT(''root'');'
       , @tcpPort VARCHAR(10) = '1433'
       , @WindowsAuth BIT = 1   -- 1 is for Windows Authentication, 0 is for SQL Server Authentication
       , @loginID VARCHAR(100) = 'loginID'
       , @password VARCHAR(100) = 'password'
       , @serverName VARCHAR(100) = 'SPACESHIP';
    
    -- /B "WindowTitle" parameters produce output in the SSSMS !!!
    SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"' 
                + ' "' + @SQL + '"' 
                + ' queryout "' + @outputFileName + '"'
                --+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)
                + CASE WHEN @WindowsAuth = 1 THEN ' -T'
                   ELSE ' -U '+ @loginID + ' -P ' + @password
                   END
                + ' -x -c -C 1252 -a 32768'
                + ' -S "' + @serverName + ',' + @tcpPort + '"';
    
    -- just to see it
    SELECT @SQLCmd AS [Command to execute];
    
    -- create file on the file system
    EXECUTE master.sys.xp_cmdshell @SQLCmd;




    Friday, September 20, 2019 7:12 PM
  • Hi ITBobbyP,

    The following link will be helpful:

    Export to XML Using SSIS.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 23, 2019 6:13 AM
  • So I did EXACTLY as the link instructed.

    The Edit Script defaults to Microsoft Visual C# 2017 rather than the instructions Microsoft Visual C# 2008.

    The C# Edit Script looks like so...

    public void Main()
    
            {
                // TODO: Add your code here
    
                string Data = Dts.Variables["DataXML"].Value.ToString();
    
                System.Xml.XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(Data.ToString());
                string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["FileName"].Value.ToString();
                xdoc.Save(outputFile);
    
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    It was giving me a red error on the new XmlDocument() saying...

    "The type or namespace name "XmlDocument" could not be found. There is a little drop-down box on it and wants to change it to...

    using System.xml or System.Xml.XmlDocument or Generate type 'XmlDocument'

    Anyways...this failed. I am by no means a C# Programmer...sooooo I'm at a total loss here...

    In the mean time, I found this possible solution...

    http://rad.pasfu.com/index.php?/archives/25-SSIS-Sql-Server-to-XML-Save-to-file.html

    And I'm going to give that a shot.

    Monday, September 23, 2019 5:59 PM
  • Hi ITBobbyP,

    I already provided you a much better solution in this thread by using bcp.

    It is using streaming and very scalable.

    You can test it directly in SSMS.

    You can launch it in SSIS by using one single SSIS Execute Process Task.

    Monday, September 23, 2019 6:26 PM
  • Yeahhhh...unfortunately I'm not going to get away with bcp in our shop here.

    I am really REALLY close. If I can just get this Edit Script to work and this C#, I should be golden.

    Monday, September 23, 2019 6:39 PM
  • Hi ITBobbyP,

    >> "...Yeahhhh...unfortunately I'm not going to get away with bcp in our shop here..."

    bcp is a part of SQL Server. What's the problem with it?

    https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017


    You can find me on LinkedIn and Skype.
    Monday, September 23, 2019 6:50 PM
  • I guess I can try and build an argument...but the bcp needs to execute a SQL Server Stored Procedure with two inputs...@StartDate_in and @EndDate_in...I honestly don't think I have ever done a bcp that executes a SQL Server Stored Procedure with Parameter inputs
    Monday, September 23, 2019 7:43 PM
  • Hi ITBobbyP,

    Here is template code for you.

    It creates a stored procedure with two parameters. The SP returns back XML. @SQL variable is ready to be used via bcp call.

    USE tempdb
    GO
    
    CREATE PROCEDURE dbo.usp_generateXML
    (
    	@StartDate_in DATE,
    	@EndDate_in DATE
    )
    AS 
    BEGIN 
    	SET NOCOUNT ON;
    
    	SELECT Name as [DatabaseName], CAST(crdate as DATE) as [CreationDate]
    	FROM master.sys.sysdatabases
    	WHERE crdate BETWEEN @StartDate_in AND @EndDate_in
    	FOR XML PATH('r'), TYPE, ROOT('root');
    END
    
    DECLARE @SQL VARCHAR(2048);
    DECLARE @StartDate_in  CHAR(10)= '2002-01-01',
    	@EndDate_in CHAR(10) = '2020-12-31';
    
    SET @SQL = 'EXEC tempdb.dbo.usp_generateXML @StartDate_in=''' + @StartDate_in + '''' +
    	', @EndDate_in=''' + @EndDate_in + ''';';
    PRINT @sql;
    

    Monday, September 23, 2019 8:11 PM
  • Okkkk....Soooo the next challenge is that the @StartDate_in and @EndDate_in Parameters will be dynamic...like last week's data for example. And it needs to be a scheduled Job. Sooooo I think you're saying just to create a SQL Server Agent Job with all this stuff in it then and NOT a SSIS Package??
    Monday, September 23, 2019 8:22 PM
  • Okkkk....Soooo the next challenge is that the @StartDate_in and @EndDate_in Parameters will be dynamic...like last week's data for example. And it needs to be a scheduled Job. Sooooo I think you're saying just to create a SQL Server Agent Job with all this stuff in it then and NOT a SSIS Package??

    >> "...You can test it directly in SSMS.

    You can launch it in SSIS by using one single SSIS Execute Process Task..."

    Obviously, you can use SSIS project and package parameters.


    Monday, September 23, 2019 8:27 PM
  • The @StartDate_in and @EndDate_in Parameters are defined as DATE in the SQL Server Stored Procedure. So if doing via SSIS, how do I do the CONVERT to VARCHAR(10)? Because in the @SQL it must be character.
    Monday, September 23, 2019 8:32 PM
  • And there in lies the problem with bcp...

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

    Monday, September 23, 2019 8:38 PM
  • CAST(@input AS VARCHAR(10))
    Monday, September 23, 2019 8:38 PM
  • Did you see the above...that's kind of what I thought was going to happen

    I think I'm going to open a new thread.

    I appreciate your help...but I'm dieing here!

    Monday, September 23, 2019 9:40 PM
  • Hi ITBobbyP,

    No need to give up so easy.

    1. 'sys.xp_cmdshell' was just for you to test manually in SSMS.
    2. SSIS doesn't need it for the Execute Process Task.
      You can just generate the @SQLCmd variable and run it at the command prompt for testing.


    Monday, September 23, 2019 10:02 PM