[SOLVED] How to loop through all tables and fields in each table to get percentage of missing values

How to loop through all tables and fields in each table to get percentage of missing values

I am trying to, using SSIS, obtain a table to get the percentage of missing values of every field in every table of a SQL Server database.

Ideally I would like to create a new table in another database with 4 fields

Table / Field / Type / PctMissingValues

with one row for each field of a table. Type would be the field type

My idea was to use foreach loop containers to loop through tables and fields, with inside the container a Data flow task consisting of OLE DB Source > Aggregate > OLE DB Destination but I can't figure out how to do this, I am new to SSIS and ETLs in general

Solution

The following SQL query generates one query per column in a database that counts total rows and rows where the value is NULL.

You can load this in to a variable and loop through it in SSIS running the statement in each row one at a time and logging the results form that query out to another table.

SELECT 
 OBJECT_SCHEMA_NAME(C.object_id) AS TableSchema
,OBJECT_NAME(C.object_id) AS TableName
,C.name AS ColumnName
,'SELECT COUNT(*) AS TotalRows, COUNT(IIF([' +C.name+ '] IS NULL,1,NULL)) AS NullRows 
    FROM [' + OBJECT_SCHEMA_NAME(C.object_id) + '].[' + OBJECT_NAME(C.object_id) + ']' AS CountQuery
FROM sys.columns AS C
INNER JOIN sys.tables AS T
    ON C.object_id = T.object_id