Exporting data from XML to a relational MR database

Data Model > Accessing the UNICOM Intelligence Data Model > Working with the Case Data Model > Accessing case data from third party applications > Transferring data using SQL Server Integration Services > Exporting data from XML to a relational MR database

This topic describes how to export data from an XML file to a relational MR database.

Before starting

Create the SQL Server database. For more information, see 3: Creating a new SQL Server database.

For information on working with the SQL Server Import and Export Wizard, see:

http://technet.microsoft.com/en-us/library/ms141209(SQL.90).aspx

Procedure

1 From the Windows Start menu, choose: Programs > SQL Server > Microsoft SQL Server Management Studio.

2 Right-click the appropriate database and select Tasks > Export Data.

This opens the first page of the SQL Server Import/Export wizard.

3 Click Next.

4 From the Data Source list, select UNICOM Intelligence DM-2 OLE DB Provider, and then click Properties.

This opens the Connection tab in the Data Link Properties dialog box.

5 From the Metadata Type list, select (None).

6 From the Case Data Type list, select UNICOM Intelligence XML Data File (read-write).

7 Enter the Case Data Location, either by typing the name and location of the .xml file, or by clicking Browse.

8 Leave the Case Data Project blank.

9 Click Test Connection.

▪Test connection succeeded means that you have entered all of the details correctly.

▪If Test connection failed is displayed, check that you have entered all of the details correctly.

10 Click OK to returns to the Choose a Data Source page of the SQL Server Import/Export wizard, and then click Next.

This opens the Choose a Destination page of the SQL Server Import/Export wizard.

11 From the Destination list, select UNICOM Intelligence DM-2 OLE DB Provider, and then click Properties.

This opens the Connection tab in the Data Link Properties dialog box.

12 From the Metadata Type list, select (None).

13 From the Case Data Type list, select UNICOM Intelligence Database (MS SQL Server) (read-write).

14 Enter the Case Data Location. This should be in the form of an OLE DB connection string, for example:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=LocalHost

For more information, see Connecting to a relational MR database using RDB DSC 2.

15 If necessary enter your Login ID and Password and click OK.

16 Enter the project name in the Case Data Project text box. Typically, this is the same as the database name.

17 Click Test Connection.

Test connection succeeded means that you have entered all of the details correctly and you have successfully connected to the database.

If Test connection failed is displayed, check that you have entered all of the details correctly and, if relevant, check your network connection to the server where the database is.

18 Click OK to return to the Choose a Destination page of the SQL Server Import/Export wizard, and then click Next.

This opens the Specify Table Copy or Query page of the SQL Server Import/Export wizard.

19 Select Copy table(s) and view(s) from the source database, and then click Next.

20 Select the VDATA table for both the Source and Destination, and then click Next.

21 Select Run immediately.

22 Deselect Save SSIS Package.

23 Click Next, and then click Finish.

SSIS exports the data in the .xml file to the relational database.

Requirements

▪UNICOM Intelligence Data Model

▪Microsoft SQL Server