You need to use a combination of the Execute SQL task and the Script Task.

The trick is to use one of the XML clauses of T-SQL to pull the data from SQL

e.g

SELECT * FROM HumanResources.Department
FOR XML AUTO, ELEMENTS
 

You then need to configure a variable to map the result set to the variable.

User::strXML - Type String

In the execute sql task you need to also to return the result set as XML on the general tab.

On the parameter mapping tab

set the Resultsetname to 0 and the variable Name to User:strXML

Then Configure a script task

   specify user::strXML as a readonlyvariable

finally in the script task

add the following code to create an xml file

Public Sub Main()

Dim sw As New IO.StreamWriter("C:\SSIS\test.xml") 

sw.Write(Dts.Variables("User::strXML").Value.ToString())

sw.Flush()

sw.Dispose()

Dts.TaskResult = Dts.Results.Success

End Sub