Tuesday, September 30, 2008

Loading XML data into SQL Server 2008

SQL Server DBA’s will often come across a scenario where they need to load data from an XML document into SQL Server 2005 or 2008. As XML is being used widely by many organizations to communicate and share data, it is becoming a common task for DBA’s to load XML data into SQL Server. This article provides a step by step guide on how to create an configure an SSIS package which can be used to import data from XML file to SQL Server 2008.

The first step is to launch BIDS (aka SQL Server Business Intelligence Development Studio). In BIDS select File | New | Projects, the will pop up the New Project screen. From here we need to select Business Intelligence Projects under the Project Types and then we need to select Integration Services Project under the Templates. Then you need to provide the SSIS Project name, location etc. Once you have entered the details left click OK to create the solution. The project creation screen will look as shown in the image below:

BIDS will open up the SSIS Package Development Studio Environment. The Screen will have a Toolbox on the left and Solution Explorer and Properties on the right side of the screen. One of the most important spaces which we need to notice is the Connection Manager space which is at the bottom of the screen. In the center of the screen you will see the work space which has four tabs namely, Control Flow, Data Flow, Event Handler and Package Explorer.

To build the SSIS package you need to drag Data Flow Task from the toolbox and drop it on the Control Flow tab. Then you need to rename the Data Flow Task to Load XML and double click on it to open the Data Flow Task. By doing so, you will notice that you are now in the Data Flow Tab of BIDS.

To buid a Data Flow, drag the XML Source from the Data Flow Sources Toolbox and drop it on the Data Flow designer surface and rename the task as XML Source Data File.

You need to double click the XML Source Data File task which will open the XML Source Editor where you need to select the XML file location as the value for Data Access Mode. Then you need to specify the actual path for the xml data file for XML Location and finally click the Generate… button to create and save the XSD file.

Next you need to Drag the SQL Server Destination Data Flow task from the toolbox and rename it to Load XML Data to SQL Server. Below are the following steps for the remainder of the configuration

1) You need to connect the green arrow from the XML Source Data File to the Load XML Data to SQL Server task 2) Double click the Load XML Data to SQL Server task to open up the SQL Destination Editor and click the New… button which will pop up the Configure OLE DB Connection Manager.

3) For a valid connection to the SQL Server Destination database one needs to click on the New… button which will once again pop up the Connection Manager where the Provider needs to be selected as Native OLE DB\SQL Server Native Client 10.0, SQL Server name, Authentication Mode and finally the destination database name. Once added all the details click OK to save the changes.

4) The table name needs to be provided where the data imported from the XML file will be stored in the destination database. You then need to click the Mappings to check whether the elements in the XML document are mapped correctly to the table column in SQL Server Table. Click OK to save the settings. The final screen will look as shown below:

5) The package screen in BIDS will look as shown in the following image:

6) Select the SSIS package named “LoadXML.dtsx” if renamed otherwise by default BIDS names it as “package.dtsx”. Right click “LoadXML.dtsx” package which will popup a window where you need to select the Execute Package option, this will run the SSIS package.

7) Once the package has successfully executed you can see the tasks turning green and it will also mention the number of records transformed and published to SQL Server.

8) The sample XML data file used in this example is shown in the following image:

9) The data which was loaded into the SQL Server Table by the SSIS package is shown below:

10) Save the SSIS package by pressing CTRL + S

No comments: