Filtering data in SQL Server Integration Services

When you use SQL Server Integration Services (SSIS) to transfer data, you may sometimes want to filter the data. There are two ways you may 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.

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.

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.

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.