There was an error in this gadget

Monday, 9 May 2011

SSIS - Sql Server to XML - Save to file

As you know there is no XML destination in SSIS.
so how you can save result of a query from sql server to XML file?
Solution is:


First Of all you can use FOR XML to get result of query in XML , look at our sample query :

SELECT EmployeeID,NationalIDNumber,ContactID
FROM HumanResources.Employee
FOR XML RAW('Facility'),Root('Extract'),Elements  
 
this will creates elements with name 'Facility', and attributes 'EmployeeID' , 'NationalIDNumber' , 'ContactID'
and the Root node is 'Extract'.
for more information about FOR XML in sql server look here .
So, start with SSIS:

1- Create a Variable of String type in package scope, and name it as XMLData. 

2- Add an Execute SQL Task, set connection as OLEDB to the AdventureWorks Database ( If you haven't AdventureWorks sample database, download it from here ),
write this query in SqlStatement:
SELECT EmployeeID,NationalIDNumber,ContactID
FROM HumanResources.Employee
FOR XML RAW('Facility'),Root('Extract'),Elements  


Set ResultSet property to XML.
then go to Result Set tab, and do this mapping:
Result Name                         Variable Name
--------------------------------------------
 0                                      User::XMLData

3- Add a Script Task after execute sql task, set language as C#. and set ReadOnlyVariables as User::XMLData .


then edit script and write this code in Main() method:
public void Main()
        {
            System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
            xdoc.InnerXml = Dts.Variables["XMLData"].Value.ToString();
            xdoc.Save(@"E:\Output.xml");

            Dts.TaskResult = (int)ScriptResults.Success;
        }
 whole schema:

Finished !
now just run the package and result of query will save in xml file specified.

No comments:

Post a Comment