Importing data from XML and exporting to IBM SPSS Statistics

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

This topic shows you how to use the SSIS Import and Export wizard to import data from an XML file and to export it to an IBM SPSS Statistics .sav file.

When you export data to IBM SPSS Statistics, you should create an SSIS package and not attempt to export the data immediately. You then need to edit the package before you can run it in SQL Server Management Studio.

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

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

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

5 From the Metadata Type list, select Intelligence Metadata Document.

6 Enter the Metadata Location, either by typing the name and location of the .mdd file into the text box, or by clicking Browse and selecting it in the Open dialog box.

7 Deselect Open metadata read/write.

8 If you want to select one or more particular versions of the metadata to use for the export, click Properties, and then select the version or versions you require. For more information, see Working with versions.

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

10 Enter the Case Data Location, either by typing the name and location of the XML file into the text box, or by clicking Browse and selecting it in the Open dialog box.

11 Leave the Case Data Project text box blank.

12 Click Test Connection.

Test connection succeeded means that you have entered all of the details correctly and you have successfully connected to the data in the XML file.

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

13 Click OK to return to the Choose a Data Source page of the SSIS Import/Export wizard, and then click Next.

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

15 From the Metadata Type list, select Intelligence Metadata Document.

16 Enter the Metadata Location, either by typing the name and location of the .mdd file into the text box, or by clicking Browse and selecting it in the Open dialog box. This should be the same .mdd file that you specified previously.

17 Select Open metadata read/write.

18 If you previously selected one or more particular versions of the metadata to use for the export, you must click Properties and select the same version or versions that you selected before. For more information, see Working with versions.

19 From the Case Data Type list, select IBM SPSS Statistics File (SAV) (read-write).

20 Enter the Case Data Location, either by typing the name of the .sav file to which you want to export the data, or by clicking Browse and selecting it in the Open dialog box. This must either be a new file, or one to which you have previously exported data using the same version or versions of the .mdd file and, if a filter was specified, the same selection of variables.

21 Leave the Case Data Project text box blank.

22 Select the Advanced tab and choose the data validation options that you require.

23 Select the Connection tab, and then click Test Connection.

Test connection succeeded means that you have entered all of the details correctly and the data can be exported to the specified .sav file.

If Test connection failed is displayed, check that you have entered all of the details in this dialog box correctly.

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

25 Do one of the following:

If you want to export only a subset of the data, select Use a query to specify the data to transfer, and then follow the instructions in Filtering data when exporting to IBM SPSS Statistics.

If you want to export all of the data, select Copy table(s) and view(s) from the source database and then continue through the instructions in this topic.

27 Select the VDATA table for both the Source and Destination.

28 If you want to append data to an existing .sav file, click the button in the Transform column. This opens the Column Mappings and Transformations dialog box. Select Append rows to destination table and then click OK. This returns you to the Select Source Tables and Views page.

30 Deselect Run immediately.

31 Select Save SSIS Package.

32 Select SQL Server, if you want to store the SSIS package onto an SQL Server installation, or Structured Storage File, if you want to store the SSIS package in a file.

This opens the Save SSIS Package page. The details on this page depend on whether you chose SQL Server or Structured Storage File on the previous page.

34 Enter a Name, Description, and the other details that are required for the SSIS package.

35 Click Next, and then click Finish.

For information about error and warning messages, see SPSS Statistics SAV DSC error and warning messages.