bicortex

How to create an XML file using SSIS and MS SQL Server

In this short presentation I will show you how to extract data out of SQL Server and convert it into an XML file. As it happens, XML is slowly becoming a de facto standard for data file storage; slowly overtaking other popular formats e.g. text, CSV. Luckily the whole process is a rather simple affair and with a little bit of SQL and SSIS hacking you should be able to spit out your XML files in no time.

Before moving onto building an SSIS solution, let’s create a sample data set to have some dummy data to work with.

CREATE TABLE SampleData
(
Log_ID int NOT NULL,
Insert_DateTime datetime NOT NULL,
[Event_Type] nvarchar (100) NOT NULL,
[Message] nvarchar (1000) NOT NULL
)
INSERT INTO SampleData
VALUES (5, '2012-10-01 23:33:01.005', 'Local Host', 'Network Connection Lost')
INSERT INTO SampleData
VALUES (8, '2012-10-01 23:33:01.033', 'Remote Client', 'Login for ClientID: ZRT199 Unsuccessful')

Now that we got a couple of rows let’s move over to BIDS environment and start building a small SSIS package to convert the above into XML and store it as a file. Firstly, create an ADO.NET connection (make sure that ADO.NET is specified otherwise you may have issues with package execution) and name it Connection1. Next, place an ‘Execute SQL Task’ container on the control flow development pane and modify its properties (General tab) to suit this task. This involves changing ‘ResultSet’ value to XML, adjusting the connection to what we have created earlier (Connection1) and creating an SQL statement. In the SQL statement section type in the following code.

DECLARE @XmlOutput xml
SET @XmlOutput =(Select*from SampleData
FORXMLAUTO,ROOT('Log_Entries'),ELEMENTS)
SELECT @XmlOutput

When run in Management Studion this should generate an XML table with one column containing all the data from the table we created earlier, as per below.

When clicked on the actual column content you should be able to see an XML syntax as per below.

Coming back to BIDS, this is how your ‘Execute SQL Task’ component (General tab) should look like.

Still modifing the ‘Execute SQL Task’ container let’s move to ‘Result Set’ tab. Here, we need to configure how the SELECT statement output will be stored. The simplest way to do this is to create a variable of a string type, give it a name and allocate the output from SELECT statement to be stored in it. To achieve that we will create a variable of a string type (a step that can be performed earlier in this process), name it XMLOutput and under ‘Result Name’ heading in the same pane we will input 0 instead of ‘NewResultName’ default value. This is really important and make sure this value is changed, otherwise your package will not execute. The completed configuration is displayed below.

Next, we need to input the variable content which stores our SELECT statement into an XML file. This is where a little bit of VB.NET or C# will go a long way. Let’s drag a ‘Script Task’ onto the pane and connect it to the previously created ‘Execute SQL Task’ container. Before we start writing code we need to make some changes to the properties. I will be using VB here so if you wish to replicate this solution make sure that Microsoft Visual Basic 2008 is selected as a preferred language and that ‘ReadWriteVariables’ contains the XMLOutput variable we created earlier as per below.

Now onto the code. A complete VB script is enclosed below. What it does (even when you’re not familiar with VB) is quite simple to follow and modify/change to suit your requirements. The important part that also does all the heavy-lifting and is responsible for script logic is between line 19 and 21. First, we declare XMLString variable, we assign the content of XMLString variable from our SSIS XMLOutput variable, we replace the tags with out own tag – and finally generate the actual file – Log_Entries.xml placing it on the c:\ drive.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()
        Dim XMLString As String = " "

        XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "")
        XMLString = "<?xml version=""1.0"" ?>" + XMLString
        GenerateXmlFile("C:\\Log_Entries.xml", XMLString)

    End Sub

    Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)

        Dim objStreamWriter As IO.StreamWriter
        Try
            objStreamWriter = New IO.StreamWriter(filePath)
            objStreamWriter.Write(fileContents)
            objStreamWriter.Close()
        Catch Excep As Exception
            MsgBox(Excep.Message)
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: .NET, SSIS, Uncategorized

Tags: .NET, Code, Microsoft, Programming, SQL, SSIS, VB.NET

This entry was posted on Sunday, April 1st, 2012 at 9:40 pm and is filed under .NET, SSIS, Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.