Filtering data when exporting to IBM SPSS Statistics
This topic describes how to create an SQL query to filter the data when you are using SSIS to export data to an IBM SPSS Statistics.sav file.
Start by following the instructions in
Exporting UNICOM Intelligence data to IBM SPSS Statistics or
Importing data from XML and exporting to IBM SPSS Statistics, until you get to the Specify Table Copy or Query page of the SQL Server Import/Export wizard. Then continue as follows:
1 Select Use a query to specify the data to transfer.
2 Click Next.
This opens the Type SQL Statement page of the SQL Server Import/Export wizard.
3 Do one of the following:
▪Click Query Builder to use the features of SSIS to specify the query.
▪Type a query into the text box manually and then click
Next. You will then skip the next five steps described in this topic. (For information on the SQL queries that are supported by the UNICOM Intelligence Data Model, see
Basic SQL queries.)
Clicking the Query Builder button opens the Select Columns page of the SQL Server Import/Export wizard. This is where you specify the variables you want to export.
4 Do one of the following:
▪If you want to export a subset of the variables in the dataset, expand VDATA and then select each variable that you want to export and click the > button between the two panes.
▪If you want to export all of the variables in the dataset, select VDATA and click the > button between the two panes.
You must include Respondent.Serial when you are exporting from a relational MR database, otherwise the export will fail.
5 Click Next.
This opens the Specify Sort Order page of the SQL Server Import/Export wizard. Do not change anything on this page.
6 Click Next.
This opens the Specify Query Criteria page of the SQL Server Import/Export wizard. This is where you specify the filter for the case data records.
7 Do one of the following:
▪If you want to export all of the case data records, select All rows.
▪If you want to filter the case data records, select Only rows meeting criteria. Then use the Column, Oper, and Value/Column boxes to specify the criteria that you want the case data to have to be included in the transfer.
For example, to select respondents whose serial number is greater than 50 and whose interviews were conducted using version 5 of the metadata:
▪Select VDATA.Respondent.Serial in the Column box, select > in the Oper box, and enter 50 in the Value/Column text box.
▪Then on the next row, select AND in the first box, VDATA.DataCollection.MetadataVersionNumber in the Column box, and = in the Oper box, and enter '5' in the Value/Column text box.
8 Click Next.
This reopens the Type SQL Statement page, which now shows the SQL query that SSIS generated from your choices.
9 Click Parse to check that the SQL is valid.
Note Some UNICOM Intelligence Data Model variable names can give syntax errors in the SQL generated by SSIS. However, you can generally get around this problem by editing the query and enclosing the variable name in double quotation marks ("). When you do this, you must also remove the text
VDATA., with which SSIS prefixes the variable names. For more information, see
Why do some variable names give a syntax error in some third-party applications, such as DTS?.
10 Click Next.
This opens the Select Source Tables and Views page of the SQL Server Import/Export wizard.
11 Select Query for the Source and enter VDATA for the Destination, and then click Next.
This opens the Save, Schedule, and Replicate Package page of the SQL Server Import/Export wizard.
12 Deselect Run immediately.
13 Select Save SSIS Package.
14 Select SQL Server, if you want to store the SSIS package onto a SQL Server installation, or Structured Storage File, if you want to store the SSIS package in a file.
15 Click Next.
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.
16 Enter a Name, Description, and the other details that are required for the SSIS package.
17 Click Next, and then click Finish.
SQL Server now saves the SSIS package.
For information about error and warning messages, see
SPSS Statistics SAV DSC error and warning messages.
Requirements
▪UNICOM Intelligence Data Model
▪Microsoft SQL Server
See also