TARGIT Quality Check Transformation is a custom component for SQL Server Integration Services. The component assists in updating missing values in data flowing through SSIS packages.

Main task solved

Replaces NULL, empty or whitespace values as data flow through the SSIS data pipeline. Can be considered a focused and simplified version of the Derived Column task that is already part of SQL Server Integration Services.

Installation

The TARGIT Quality Check Transformation component is included in the "TARGIT Custom SSIS Tasks" installation package found in the TARGIT App & Gauge Store or the Download Center on the TARGIT Portal. After installation, a restart of SQL Server Data Tools and the SSIS project is required for the task(s) to appear in the SSIS toolbox.

Advantages

As opposed to other strategies for replacing flawed column values, e.g. updating data in existing database tables, the QC Transformation component works on data that is already in the data pipeline. This has significant performance advantages. The component passes data through without name changes or addition of extra columns, so it can easily be injected into existing data flows without resulting in additional work.

How to use

TARGIT QC Transformation is a data pipeline component and is therefore used in Data Flow tasks only. It needs a data flow input to work. To start, add the component to the Data Flow task, connect to a previous data flow task and double click to open.



The conditions and values for replacing undesired values depend on data types. The following conditions are available:

  • (When) NULL
  • (When) NULL or Empty
  • (When) NULL, Empty or Whitespace


As each column is selected, available conditions are automatically listed and a default replace value set. Also, relevant metadata about the column is displayed at the bottom of the UI window. A column can be removed from check using the x to the far right. Any replacement value input will be validated against the current data type of the column selected.

If input columns are no longer available, they are automatically removed when the task is next opened. At runtime, the task will continue to run, but definitions for input columns no longer available will be ignored.





  • In later Data Tools (Visual Studio) versions, check the TargetServerVersion property for the SSIS project if the TARGIT components do not show up in the SSIS Toolbox
  • If the TARGIT components are not in the SSIS Toolbox after installation, a trouble shooting article is available (see Related articles)
  • The TARGIT custom components are available for SQL Server versions beginning at 2005
  • The components are free to use, but only officially supported for active TARGIT customers