Share via

Readme_Process XML Data Package Sample

  • Article

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.

The Process XML Data sample is a package that performs several sequential operations on a single XML data source. The package loads XML data, obtains a subset of the data by using an XPath operation, transforms the data to a standard flat file format using an XSLT operation, and finally writes the data to a text file. The package uses two XML tasks, one for the XPath operation, and one for the XSLT operation. The first XML task writes its result to a variable, which the second XML task uses as its data source.

If you run the sample on a non-English version of Windows, you may have to substitute the localized name of the Program Files folder to open or run the sample.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

  • The sample package and data files that it uses must be installed on the local hard disk drive.
  • If you intend only to run the sample package from the command line, you must install Integration Services.
  • If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.

For more information about how to install samples, see Readme_Installing Sample Integration Services Packages in SQL Server Books Online.

Location of the Sample Package

If the samples were installed to the default installation location, the Process XML sample package is located in the following folder:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ProcessXMLData Sample\ProcessXMLData\

The following files are required to run this sample package.

File Description

ProcessXMLData.dtsx

The sample package.

Orders.xml

The source XML data.

TransformToFF.xslt

The XSLT style sheet used to transform the persisted XPath query results to a standard flat file.

XSLTResults.txt

The text file that the sample writes to.

Running the Sample

The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.

For this sample, you may have to update "Program Files" in the ConnectionString property for the Create_Execute_Process_Dest and customers connection managers.

To run the package by using dtexec

  1. Open a Command Prompt window.

  2. Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of dtexec.

  3. Type the following command:

    dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ProcessXMLData Sample\ProcessXMLData\ProcessXMLData.dtsx"

  4. Press ENTER.

For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.

To run the package in Business Intelligence Development Studio

  1. Open Business Intelligence Development Studio.

  2. On the File menu, point to Open, and then click Project/Solution.

  3. Locate the ProcessXMLData Samplefolder, and then double-click the file named ProcessXMLData.sln.

  4. In Solution Explorer, right-click ProcessXMLData**.**dtsx in the SSIS Packages folder, and then click Execute Package.

Components in Sample

The following table lists the tasks, used in the sample.

Element Purpose

XML task

The XML task, Perform XPath Operation, performs the XPath operation that extracts the orders for France and Mexico.

The XML task, XSLT to Text File, formats the data.

File connection manages

Three File connection managers connect to the XML, XSLT, and text files.

Sample Results

The execution results of this sample package are saved to a flat file named xsltResult.txt, which is located in the following folder:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ProcessXMLData Sample\DataFiles