In this blog I showed how to read the contents of a file into a variable.
Recently a customer asked how to do the opposite. He needs to query an SQL table with an XML result and write the results to a file.
Here's how:
- Drop an Execute SQL Task onto the designer.
- Double click to open the task.
- Set result set to XML
- Set connection type to whatever type you wish to use
- Create or use an existing connection, I'm using the AdventureWorks sample database.
- Set SQLSourceType correctly. In this case, I used Direct Input
- Set the SQLStatement. The following is an example:
- SELECT CurrencyCode, Name, ModifiedDate
FROM Sales.Currency
FOR XML AUTO
- Select the Result Set node on the left of the TaskUI
- Click "Add" and set the Result Name to zero (0)
- Set the Variable name to the one you want to contain the XML
That's it for the Execute SQL Task.
Now, to get the resulting XML into a file, do the following:
- Drop a Script Task onto the designer.
- Open the Task UI by double clicking
- In the Script node, add the name of the variable that holds the XML you set above in the Execute SQL Task to the ReadOnlyVariables. I used RESULTSXML
- Write a snippet of code to write out the contents into a file. I've hardcoded everything to keep it simple. Don't do that in production code.
-
Public Sub Main()
Dim errorInfo As String = ""
Dim Contents As String = ""
Contents = Dts.Variables(
"RESULTSXML").Value.ToString()
WriteVariableContents(
"C:\\TEMP\\resultsxml.xml", Contents)
End Sub
Public Sub WriteVariableContents(ByVal filePath As String, ByVal contents As String)
Dim objWriter As IO.StreamWriter
Try
objWriter =
New IO.StreamWriter(filePath)
objWriter.Write(contents)
objWriter.Close()
Catch Ex As Exception
MsgBox(Ex.Message)
End Try
End Sub
That's pretty much it in a nutshell.
HTH
Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden