Correcting Not NULL issue

The Quality Check Task must be inserted into the correct position of the Control Flow. Ideally, this check should be performed as the last thing before processing the OLAP database, after all other manipulations of the tables.

Try and run the SSIS package. The Quality Check Task may fail at this point, and if it does, you will be able to get some information from the Progress while it is still running in Debug mode.

The reason for this is actually due to the design of the source database, the CRONUS database. When we imported tables into the Stage database, we simply copied the design from the CRONUS database, and it appears that this database was using a design that did not allow null values (blanks) in many of its columns.

If e.g. we looked up the Item table in the Stage database we would see that almost all columns has been designed to not allow null values.

This poses a great problem to us, because when running the Quality Check Task, and finding keys are missing, we would want to insert these keys into e.g. the Item table. When inserting a new key we actually at the same time create a new row in the table, and if only the key column is updated with a value, all the remaining columns will be empty/blank/null. And when the constraint has been set to not allow null values, we will get an error.

Luckily, there is a fairly easy way to correct this through the SQL Server Management Studio: By running a script to drop and recreate the table – and to make sure that the new table does not have the NOT NULL restriction.

Right click the table that you want to correct and select the script to drop and create the table.

This will produce a script that in effect drops the current table and immediately recreates it with the exact same structure. However, we do not want the exact same structure – we want ‘NOT NULL’ columns to actually accepts NULL values.

Therefore, before executing the script, we replace all ‘NOT NULL’ references with ‘NULL’ references.

Search and Replace (CTRL + H) to replace ‘NOT NULL’ with ‘NULL’.

Finally, click the ‘Execute’ button to run the script.

After having done this for all existing tables, we should now have a Data Warehouse free of NULL constraints.

The DROP statement of course wipes all data from the tables, so they will be empty at this point. To re-populate the tables, we may simply run the full SSIS package again.

So, run the SSIS package, and once completed without further errors, open the Item table in SQL Server Management Studio. At the bottom of the table we will notice that 3 new products have been added. These 3 products are actually foreign keys that existed in the Sales Invoice Line fact table, but were missing in the Item dimension table.

Now that we seem to have eliminated all inconsistency issues we should change the Analysis Services Processing task of our SSIS package so that it no longer ignores errors. In that way we make sure that we will be made aware of any inconsistency issues we may introduce in the future (e.g. by adding new tables).

Change Analysis Services Processing task settings back to ‘Default error configuration’ to make sure that any potential, future inconsistency issues are reported as errors.