Filtering data in SQL Server Integration Services
When you use SQL Server Integration Services (SSIS) to transfer data, you might sometimes want to filter the data. There are two ways you might want to do this:
▪Filtering the variables to transfer: for example, when you want to exclude some of the system variables from the transfer.
▪Filtering the case data records to transfer: for example, when you want to restrict the transfer to respondents whose serial number (Respondent.Serial) is greater than 2000, or who were interviewed using a particular version of the questionnaire definition (which you would specify using the DataCollection.MetadataVersionNumber system variable).
The exact details of how you specify filters in the SQL Server Import/Export wizard and whether they are appropriate varies according to the type of data to which you are exporting, and therefore the DSC you are using.
SPSS Statistics SAV DSC
You can filter both the variables and the case data records when transferring data to an IBM SPSS Statistics
.sav file. Moreover, the way you define the filters in SSIS is relatively straightforward. For full details, see
Filtering data when exporting to IBM SPSS Statistics.
Quantum DSC
You can filter both the variables and the case data records when transferring data to Quantum. However, there are a number of issues:
▪You need to set up card and column definitions for all of the variables whether you want to export them or not. However, you can use
Metadata Model to Quantum to specify card and column definitions for the variables that you do not want to export at the end. If you do not do this, the ASCII file will have blank columns, but this should not cause any problems.
▪If you use Metadata Model to Quantum to generate a Quantum specification, you will need to edit it to remove the tab and l statements that relate to any excluded variables.
▪Although specifying the filter in the SQL Server Import/Export wizard is relatively straightforward, there is an additional step in which you need to perform a special edit on the SSIS package. For full details, see
Filtering data when exporting to Quantum.
Relational MR Database CDSC
You generally need to use an
.mdd file when you access the data in a relational MR database. However, because UNICOM Intelligence does not currently provide a tool for filtering an
.mdd file, it is not usually appropriate to filter the variables when you export data to a relational MR database. However, it is possible to filter the case data records. For more information on exporting data to a relational MR database, see
Creating a relational MR database from a Quanvert database,
Creating a relational MR database from an IBM SPSS Statistics.sav file, and
Exporting data from XML to a relational MR database.
See also
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.
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
Filtering data when exporting to Quantum
This topic describes how to create an SQL query to filter the data when you are using SSIS to export data to a Quantum ASCII file. For information about the issues involved when you use a filter when you export data to Quantum, see
Filtering data in SQL Server Integration Services.
Start by following the instructions in
Exporting UNICOM Intelligence data to Quantum, 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, and then click Next.
This opens the Type SQL Statement page of the SQL Server Import/Export wizard.
2 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.
3 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.
4 Click Next.
This opens the Specify Sort Order page of the SQL Server Import/Export wizard. Do not change anything on this page.
5 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.
6 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.
7 Click Next.
This reopens the Type SQL Statement page, which now shows the SQL query that SSIS generated from your choices.
8 Click Parse to check that the SQL is valid.
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 will also need to 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?.
9 Click Next.
This opens the Select Source Tables and Views page of the SQL Server Import/Export wizard.
10 Select Query for the Source and VDATA for the Destination.
11 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.
15 Click Next.
This opens the Save SSIS Package page.
16 Enter a Name, Description, and the other details that are required for the SSIS package.
17 Click Next, and then click Finish.
SSIS now saves the SSIS package.
If you chose to export some, but not all, of the variables, you must edit the SSIS package before you can run it (see
Editing the SSIS package). If you filtered only the case data records and not the variables, you do not need to do this, and you can run the package immediately.
See also