The Filter DataClosed The Filter Data tool extracts a subset of data from a recordset produced by a Database Query (ODBC), Database Query (OLEDB), or Call Stored Procedure (OLEDB) task step based on configured filter rules. A new recordset is created and provided for use in further steps. tool does not require any global configuration; it can be immediately added to a task.

To add a new Filter Data step to an existing task:

  1. Open the relevant task (if creating a new task, see How to Create a Basic Task).
  2. Either:
► Click and drag the Filter Data icon from the Task Browser to the task Design area.

–OR–

► From the task's Design tab, right-click on empty space and select New > General > Filter Data.
  1. In the General tab, Name the task step and choose the data source.

    The General tab is used to Name and describe (Description) this task step.

    Tip: If this task instance makes use of two or more Filter Data steps, ensure the Name used is unique for each individual step.

    ►Input Recordset — Displays a list of available recordsets from other task steps that the Filter Data step can use

    For more information, go to the main General tab topic.

  2. Go to the Main tab and Add the relevant filter:
    1. Select Repository Filter and click OK.
    2. In the General tab, Name your filter and select a Repository.
      ►Name — Enter meaningful name for this filter
      ►Description — If required, enter a description of this filter
      ►Repository — Displays the Repository for this filter. Use Browse to select a new Repository.

      You create new Repositories in the Output step of this task.

      For more information, go to the main General tab topic.

    3. Go to the Mapping tab and map the recordset columns to the filter columns.
      ►Repository Column — Displays the Repository column name.
      ►Recordset Column — Displays the recordset column name which the Repository column is mapped to. This is initially selected automatically if the column names are the same or displays None if it is not. However clicking on the name displays a drop down list of all available columns in the recordset for selection. Each row for the mapped column value created at task run-time is then processed by the filter and stored in the Repository against the selected Repository column name.

      Note: Repository "key" columns should not be mapped to non-unique columns to ensure that duplicate rows are not memorised.

      ►Parameter — Displays the parameter for the selected column. The parameter is resolved for each row in the record source and is used to look up existing entries in the Repository when the task is run. This is the mechanism used to detect new rows, if rows have changed, and so on.

      For more information, go to the main Mapping tab topic.

    4. Go to the Processing tab and choose how recordset columns are processed.
      ►Process all rows — All rows in the recordset are included in the subset of data provided by the step.
      ►Process new rows — Only new rows are included in the subset of data provided by the step.
      ►Process changed rows — Only changed rows are included in the subset of data provided by the step.
      ►Process unchanged rows — Only unchanged rows are included in the subset of data provided by the step.
      ►Process rows last processed more than — This option is used in conjunction with two fields below it which enable a time period to be selected. Any rows that have been previously processed more than the selected time period are included in the subset of data provided by the step.
      ►Process rows no longer present — Any rows that are no longer present are included in the subset of data provided by the step.
      ►Process based on a rule — All rows in the recordset are compared against those in the Repository. An Edit button is provided to open the a Rule Script interface to enable a rule to be manually scripted. The actual rule logical expression itself is then displayed in the field provided and is applied when the recordset is processed.

      For more information, go to the main Processing tab topic.

    5. Go to the Purging tab and choose how processed columns are purged from the Repository.
      ►Do not purge — No rows in the Repository are deleted.
      ►Purge rows no longer present — All rows in the recordset are compared against those in the Repository. Rows that appear in the Repository but not in the recordset are removed from the Repository.
      ►Purge rows no longer present if processed more than — All rows in the recordset are compared against those in the Repository. Rows that are no longer present which were processed more than the selected time period ago are removed from the Repository. For example, if a time period of 1 Minute is selected then rows that are no longer present that were last processed more than 1 minute previously are removed.
      ►Purge row no longer present rows after being processed as 'row no longer present' — All rows in the recordset are compared against those in the Repository. Rows that appear in the Repository for a second time with a status of row no longer present are removed from the Repository.
      ►Purge rows no longer present based on a rule — All rows in the recordset are compared against those in the Repository. Any matching the selected rule are removed.

      For more information, go to the main Purging tab topic.

    6. Click OK.
    1. Select Visual Basic Filter and click OK.
    2. In the General tab, Name your filter.
    3. Go to the Script tab and enter the required script.
    4. Click Test to check the script syntax.
    5. Click OK.

  3. Go to the Options tab and select the relevant error handling options for this task step.

    The Options tab allows you to define how errors in this step are handled at task runtime.

    If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task).

  4. Click  to save the task step.