Writing XML data to the File System with SSIS

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 50%

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

$29.99    $15.00
by Jayaram Krishnaswamy | November 2009 | Microsoft

This article by Dr. Jayaram Krishnaswamy, shows how you may retrieve XML data from a relational database and write it to a folder on your file system as a text or xml file using Microsoft SQL Server Integration Services 2008. The following are the different steps involved in this EL process (there being no transformation):

  • Creating a stored procedure that retrieves XML
  • Creating a package in BIDS or Visual Studio 2008
  • Adding and configuring a ExecuteSQL task
  • Adding and configuring a Script task
  • Running the package and verifying the results

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

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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 :

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt


Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

ICEfaces 1.8: Next Generation Enterprise Web Development
ICEfaces 1.8: Next Generation Enterprise Web Development

Microsoft Dynamics AX 2009 Programming: Getting Started
Microsoft Dynamics AX 2009 Programming: Getting Started

Learning jQuery 1.3
Learning jQuery 1.3

Backbase 4.4.x RIA Development
Backbase 4.4.x RIA Development

jQuery 1.3 with PHP
jQuery 1.3 with PHP

Drupal 6 JavaScript and jQuery
Drupal 6 JavaScript and jQuery

jQuery UI 1.7: The User Interface Library for jQuery
jQuery UI 1.7: The User Interface Library for jQuery


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software