TARGIT Quality Check Task

TARGIT Quality Check is a custom component for SQL Server Integration Services. The component assists in generating scripts for ensuring column and referential integrity in a relational SQL Server data warehouse database. The component works with SQL Server databases only.

Main tasks solved

Maintains referential integrity in the relational database to ensure that applications accessing the data can do so without transactional data loss, e.g. when using inner joins.

Installation

The TARGIT Quality Check 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.

Tabs

1. Values: Maintenance of central lists of data values for updating missing data values
2. Empty: Insertion of dummy data rows in empty tables
3. Tables: Set up columns to be updated as well as foreign/primary key relations between tables

Values

Centralized maintenance of a replace value repository. Changing current values of the repository will permeate the entire quality check script. Any number of value definitions can be configured, e.g. if detailed values are required for specific tables. A default value definition is always available on task creation.

Empty

The Empty functionality has limited relevance on later installations. Originally designed for early versions of SQL Server Analysis Services which did not work if fact tables were empty. Selecting a table in the list will insert a dummy row in the table if the table is empty.

Tables

Used for selecting columns to be updated and for describing relations (foreign key->primary key) between tables. The primary advantage of using the task to describe table relations, especially in complex snowflake data model scenarios, is that the order in which referential updates are run in the script generated is automatically resolved. Therefore, the order in which relations between tables are added is not relevant. The task will handle the correct execution order for referential integrity row inserts.

Use cases

Getting started

• In the SSIS package, create an ADO.NET connection manager for the SQL Server database if not already available
• Drag the task from the SSIS Toolbox to the package Control Flow
• Double click the task and select the ADO.NET connection created. The "Invalid connection" message disappears.
  
  

Setting up re-usable values

A default re-usable values item is available when the QC component is first added. Right click anywhere in the list box to add, duplicate, delete or change value items. Any value item has replacement values for data of main types Numeric, Byte (single character string), Short (string), Long (string) and Date. In the example screenshot, a set of values has been created for use in checks in customer related data.

Appending dummy rows to empty tables

The use case for the empty tables check is probably rare. If the Perform Empty Tables Check is selected, the empty tables check uses a value item set to automatically insert a dummy row for all tables in the database. The value to insert in each column is automatically detected and applied. If a table is not to be checked for empty, it can be de-selected in the table list.

Adding a table for simple column checks

Consider Example 1. To update NULL or blank values in the data, add each table to the table tree by right clicking in the empty list on the left. No particular order is necessary.

For each table, add the column to update in the Update Columns list. E.g. TerritoryCode for the customer table. As a column is selected in the list, the Update column properties window pops up. Select the value set and specific value to use for updating bad values in the column. At any time, the resulting update script that will be executed runtime is reviewable using the Show SQL button.

Adding tables for referential integrity checks

Consider Example 1. To establish full referential integrity, primary keys and relations between tables need to be defined. For each relation, dummy rows will be added to primary key (related) table to ensure that inner joins between the tables will return all transactions.

First, select the columns of the primary key of each snowflaked table using the Primary Key Columns list. Composite keys are supported.

Now, relations to the table can be defined by right clicking the table, then selecting Add Relation. Any number of tables can be added to check multiple tables against each table. Finally, select the column(s) of the foreign key relating to the table. Repeat for all relations in the table schema.

When checking the completed SQL statement, notice that the execution order is not the same as the order in which relations are added. The component will automatically resolve the order in which statements need to be executed for the insertion of rows to be made correctly.

Also, observe how the Update columns are re-used for insertion values in the dummy rows. This means that extra columns may need to be defined to get values into all columns of the added rows. Especially foreign key columns that may be used for referencing other tables of the schema.

Related articles

• Page:
  TARGIT Custom Tasks - Problem solving
• Page:
  TARGIT Period Table Task
• Page:
  TARGIT Quality Check Task
• Page:
  TARGIT Quality Check Transformation
• Page:
  TARGIT Enumeration Task