{"id":799,"date":"2012-04-01T21:40:11","date_gmt":"2012-04-01T21:40:11","guid":{"rendered":"http:\/\/bicortex.com\/?p=799"},"modified":"2013-12-02T06:08:30","modified_gmt":"2013-12-02T06:08:30","slug":"how-to-create-an-xml-file-using-ssis-and-ms-sql-server","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-create-an-xml-file-using-ssis-and-ms-sql-server\/","title":{"rendered":"How to create an XML file using SSIS and MS SQL Server"},"content":{"rendered":"

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.<\/p>\n

Before moving onto building an SSIS solution, let\u2019s create a sample data set to have some dummy data to work with.<\/p>\n

\r\nCREATE TABLE SampleData\r\n(\r\nLog_ID int NOT NULL,\r\nInsert_DateTime datetime NOT NULL,\r\n[Event_Type] nvarchar (100) NOT NULL,\r\n[Message] nvarchar (1000) NOT NULL\r\n)\r\nINSERT INTO SampleData\r\nVALUES (5, '2012-10-01 23:33:01.005', 'Local Host', 'Network Connection Lost')\r\nINSERT INTO SampleData\r\nVALUES (8, '2012-10-01 23:33:01.033', 'Remote Client', 'Login for ClientID: ZRT199 Unsuccessful')\r\n<\/pre>\n
Now that we got a couple of rows let\u2019s 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 \u2018Execute SQL Task\u2019 container on the control flow development pane and modify its properties (General tab) to suit this task. This involves changing \u2018ResultSet\u2019 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.<\/p>\n
\r\nDECLARE @XmlOutput xml\r\nSET @XmlOutput =(Select*from SampleData\r\nFORXMLAUTO,ROOT('Log_Entries'),ELEMENTS)\r\nSELECT @XmlOutput\r\n<\/pre>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
When clicked on the actual column content you should be able to see an XML syntax as per below.<\/p>\n
\"\"<\/a><\/p>\n
Coming back to BIDS, this is how your \u2018Execute SQL Task\u2019 component (General tab) should look like.<\/p>\n
\"\"<\/a><\/p>\n
Still modifing the \u2018Execute SQL Task\u2019 container let\u2019s move to \u2018Result Set\u2019 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 \u2018Result Name\u2019 heading in the same pane we will input 0 instead of \u2018NewResultName\u2019 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.<\/p>\n
\"\"<\/a><\/p>\n
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\u2019s drag a \u2018Script Task\u2019 onto the pane and connect it to the previously created \u2018Execute SQL Task\u2019 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 \u2018ReadWriteVariables\u2019 contains the XMLOutput variable we created earlier as per below.<\/p>\n
\"\"<\/a><\/p>\n
Now onto the code. A complete VB script is enclosed below. What it does (even when you\u2019re 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.<\/p>\n
\r\nImports System\r\nImports System.Data\r\nImports System.Math\r\nImports Microsoft.SqlServer.Dts.Runtime\r\n\r\n<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _\r\n<System.CLSCompliantAttribute(False)> _\r\nPartial Public Class ScriptMain\r\n    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n\r\n    Enum ScriptResults\r\n        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success\r\n        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n    End Enum\r\n\r\n    Public Sub Main()\r\n        Dim XMLString As String = " "\r\n\r\n        XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("<\/ROOT>", "")\r\n        XMLString = "<?xml version=""1.0"" ?>" + XMLString\r\n        GenerateXmlFile("C:\\\\Log_Entries.xml", XMLString)\r\n\r\n    End Sub\r\n\r\n    Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String)\r\n\r\n        Dim objStreamWriter As IO.StreamWriter\r\n        Try\r\n            objStreamWriter = New IO.StreamWriter(filePath)\r\n            objStreamWriter.Write(fileContents)\r\n            objStreamWriter.Close()\r\n        Catch Excep As Exception\r\n            MsgBox(Excep.Message)\r\n        End Try\r\n        Dts.TaskResult = ScriptResults.Success\r\n    End Sub\r\nEnd Class\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,50,1],"tags":[23,12,18,24,49,13,22],"class_list":["post-799","post","type-post","status-publish","format-standard","hentry","category-net","category-ssis","category-uncategorized","tag-net","tag-code","tag-microsoft","tag-programming","tag-sql","tag-ssis","tag-vb-net"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=799"}],"version-history":[{"count":15,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/799\/revisions"}],"predecessor-version":[{"id":2024,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/799\/revisions\/2024"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=799"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}