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.
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.
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.
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.
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.
