Writing XML data to the File System with SSIS

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

Integrating data into applications or reports is one of the most important, expensive and exacting activities in building enterprise data warehousing applications. SQL Server Integration Services which first appeared in MS SQL Server 2005 and continued into MS SQL Server 2008 provides a one-stop solution to the ETL Process. The ETL Process consists of extracting data from a data source, transforming the data so that it can get in cleanly into the destination followed by loading the transformed data to the destination source. Enterprise data can be of very different kinds ranging from flat files to data stored in relational databases. Recently storing data in XML data sources has become common as exchanging data in XML format has many advantages.

Creating a stored procedure that retrieves XML

In the present example it is assumed that you have a copy of the Northwind database. You could use any other database. We will be creating a stored procedure that selects a number of columns from a table in the database using the For XML clause. The Select query would return an XML fragment from the database. The next listing shows the stored procedure.

Create procedure [dbo].[tst]
as
Select FirstName, LastName, City from Employees
for XML raw

The result of executing this stored procedure[exec tst] in the SQL Server Management Studio is shown in the next listing.

<row FirstName="Nancy" LastName="Davolio" City="Seattle"/>
<row FirstName="Andrew" LastName="Fuller" City="Tacoma"/>
<row FirstName="Janet" LastName="Leverling" City="Kirkland"/>
<row FirstName="Margaret" LastName="Peacock" City="Redmond"/>
<row FirstName="Steven" LastName="Buchanan" City="London"/>
<row FirstName="Michael" LastName="Suyama" City="London"/>
<row FirstName="Robert" LastName="King" City="London"/>
<row FirstName="Laura" LastName="Callahan" City="Seattle"/>
<row FirstName="Anne" LastName="Dodsworth" City="London"/>

Creating a package in BIDS or Visual Studio 2008

You require SQL Server 2008 installed to create a package. In either of these programs, File | New | Projects... brings up New Project window where you can choose to create a business intelligence project with a Integration Services Project template. You create a project by providing a name for the project. Herein it was named XMLquery. After providing a name and closing the New Project window the XMLquery project will be created with a default package with the file name, Package.dtsx. The file name can be renamed by right clicking the file and clicking OK to the window that pops up regarding the change you are making. Herein the package was named XmlToFile.dtsx. The following figure shows the project created by the program. When the program is created the package designer surface will be open with a tabbed page where you can configure control flow tasks, Data Flow Tasks and Event handlers. You can also look at the package explorer to review the contents of the package. The reader may benefit by reviewing my book, Beginners Guide to SQL Server Integration Services, on this site.

Writing XML data to the File System with SSIS

Adding and configuring a ExecuteSQL task

Using an ExecuteSQL Task component the stored procedure on the SQL Server 2008 will be executed. The result of this will be stored in a package variable which will then be retrieved using a Script Task. In this section you will be configuring the ExecuteSQL Task.

Drag and drop a Execute SQL Task under Control Flow items in the Toolbox on to the Control Flow tabbed page of the package designer. Double click Execute SQL Task component in the package designer to display the Execute SQL Task Editor as shown.

Writing XML data to the File System with SSIS

It is a good practice to provide a description to the task. Herein it is, "Retrieving XML from the SQL Server" as shown. The result set can be of any of those shown in the next figure. Since the information that is retrieved running the stored procedure is XML, XML choice is the correct one to choose.

Writing XML data to the File System with SSIS

The stored procedure is on the SQL Server 2008 and therefore a connection needs to be established. Leave the connection type as OLE DB and click on an empty area along the line item, Connection. This brings up the Configure OLE DB Connection Manager window where you can select an existing connection, or create a new connection. Hit the New... button to bring the Connection Manager window as shown. The window comes up with just the right provider [Native OLE DBSQL Server Native Client10.0]. You can choose the server by browsing with the drop-down handler as shown. In the present case the Windows Authentication is used with the current user as the database administrator. If this information is correct you can browse the database objects to choose the correct database which hosts the stored procedure as shown. You may also test the connection with the Test Connection button. You must close the Connection Manager window which will bring you back to the Configure OLE DB Connection Manager window which now displays the connection you just made. To proceed further you need to close this window as well.

Writing XML data to the File System with SSIS

This will bring in the connection information into the Execute SQL Task editor window. The type of input is chosen to be a direct input (the others are file and variable). The query to be executed is the stored procedure, tst described early in the tutorial. The BypassPrepare is set to false. The General page of the Execute SQL Task editor is as shown here.

Writing XML data to the File System with SSIS

Since there are no parameters for the procedure this tabbed page may be skipped. Click on result set. The result set that is returned by the stored procedure will be assigned to a user variable as shown in the next figure.

Writing XML data to the File System with SSIS

This is configured by invoking (clicking on an empty area under list item Variable Name and choosing) the Add Variable window as shown in the next figure. You can set the scope of the variable, its type and value in this screen.

Writing XML data to the File System with SSIS

After adding this variable it is necessary to make this variable available to the SQL statement. Click on Expressions on the left to bring up the page for Expressions as shown.

Writing XML data to the File System with SSIS

Click along the line item Expressions inside this window to bring up the Property Expressions Editor and click inside list item Property from which you can choose the SQLStatementSource as shown.

Writing XML data to the File System with SSIS

Click inside the list item Value to bring up the Expression builder. Expand the Variables node. Drag and drop @[User:Variable] to the text area box Expression: as shown. If you hit the Evaluate Expression button you would see the value tst in the text area Evaluated Value. This happens to be the stored procedure that is evaluated. The expression @[User:Variable] gets into the Property Expressons Editor. Close out these windows.

Writing XML data to the File System with SSIS

Adding and configuring a Script task

You will use the variable you created in the previous task in a Script Task to display the variable in a message box as well as write the variable to a file(text and /or XML for example).

Drag and drop a Script Task adjacent to the Execute SQL Task from the Toolbox under ControlFlow Items. You also want to make sure that the Script Task runs if the Execute SQL Task succeeds. To make sure this happens you add a constraint as shown in the figure by dragging the green dangling arrow from Execute SQL Task to Script Task.

Writing XML data to the File System with SSIS

Double click the Script Task component to bring up the Script Task Editor. The Script page of the Editor gets displayed as shown.

 

The script language by default is Microsoft Visual Basic 2008. The entry point is as usual "Main" as used in scripts. It can make use of ReadOnly Variables or ReadWrite Variables. Here the intention is to use ReadOnly variable. Click on an empty area along ReadOnly Variables to open the Select Variables window as shown. Place a check mark for the @[User Variable] as shown and click OK. This information gets into the Script Task Editor. Observe that you can use more than one variable provided they are separated by commas.

Writing XML data to the File System with SSIS

Click on the Edit Script... button to bring up the script editor. This opens a much larger window Integration Services Script Task. This is a template where all you need to do is to add your code at the indicated position. Since intellisense is supported it should be relatively easy to write code.

Writing XML data to the File System with SSIS

Just add the statement shown in bold at the indicated position so that the Main() function is as shown.

Public Sub Main()
'
' Add your code here
MessageBox.Show(Dts.Variables(0).Value)
Dts.TaskResult = ScriptResults.Success
End Sub

Save the page. Close the "Integration Services Script Task" editor window as well as the "Script Task Editor" window.

Running the package and verifying the results

Build the project using the menu item, Build. Right click the XMLToFile.dtsx and from the drop-down click Execute Package drop-own item. The program processes the request and after a while, The result gets displayed in a message box as shown.

Writing XML data to the File System with SSIS

To write the message to the file system add the following snippet below the message box statement so that the Main() function appears as shown.

Public Sub Main()
'
' Add your code here
MessageBox.Show(Dts.Variables(0).Value)
My.Computer.FileSystem.WriteAllText("C:XML_SSIS.txt",
Dts.Variables _(0).Value.ToString, False)
My.Computer.FileSystem.WriteAllText("C:XML_SSIS.xml",
Dts.Variables _(0).Value.ToString, False)
Dts.TaskResult = ScriptResults.Success

End Sub

Build the project and execute the package as before. The message box shows up as well as you can verify that two files XML_SSIS.text and XML_SSIS.xml are written to your hard drive.

Summary

This tutorial shows how you may retrieve XML data from your SQL Server 2008 and write it to your hard drive by creating a SQL Server Integration Services package consisting of a ExecuteSQL task and a script task. The XML is retrieved by a stored procedure on the SQL Server.

 

If you have read this article you may be interested to view :

Books to Consider

comments powered by Disqus