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.
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 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 need to edit the SSIS package as specified below, before you can run it. 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