In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.
Below is my Stored procedure:
Below is my Stored procedure:
CREATE PROCEDURE Get_Versions AS BEGIN SET NOCOUNT ON DECLARE @Tmp TABLE (ID INT IDENTITY(1,1),Product VARCHAR(100)) INSERT @Tmp SELECT 'SQL Server 2012' INSERT @Tmp SELECT 'SQL Server 2014' INSERT @Tmp SELECT 'SQL Server 2016' SELECT (SELECT * FROM @Tmp FOR XML PATH('Versions')) XmlData END
Stored Procedure output will be like shown below:
<Versions> <ID>1</ID> <Product>SQL Server 2012</Product> </Versions> <Versions> <ID>2</ID> <Product>SQL Server 2014</Product> </Versions> <Versions> <ID>3</ID> <Product>SQL Server 2016</Product> </Versions>
I have added Data Flow Task on Control Flow tab and
OLE DB Source and Flat File Destination on Data Flow tab and Data Conversion task between source & destination.
Below are the respective task settings:
1 comment:
Nice try..this doesnt use the ssis to its full capability..is there any other way...
Post a Comment