A TSQL helper stored proc to generate and insert your unknown records for you. Be sure to modify and test the stored proc before implementing it. It can be tested by not supplying the third @action parameter, and it can be executed by specifying a third @action parameter of ‘runit’. Credit to http://www.bidn.com/blogs/PatrickLeBlanc/ssis/745/ins…

UnknownRecord.sql

-- author: [email protected]
-- create: 2013-03-08
-- description: this proc will take table name, schema name, and optional action parameters
--  		and insert an "unknown" record into that dimension table for you. If you don't
--		specific the @Action parameter as 'runit', then it will only print the SQL
--		output for you.

create proc [helper].[UnknownRow]  
      @TableName sysname, 
      @TableSchema sysname = 'dbo', 
      @Action varchar(10) = 'just print' 
as 
declare 
      @ColumnListing varchar(max) = '', 
      @ValuesList varchar(max) = '', 
      @query varchar(max)=' ',
      @SkColumnName varchar(250) = reverse(substring(reverse(substring(@tablename,4,len(@tablename))),1,LEN(@tablename)))+'Key'
if (@TableName not like '%DimDate%')
begin
	set @query = @query+ 'IF NOT EXISTS (SELECT * FROM ['+@TableSchema+'].['+@TableName+'] WHERE '+@SkColumnName+' = -1)' 
	set @query = @query+ ' begin' 
	set @query = @query+ ' set identity_insert ['+@TableSchema+'].['+@TableName+'] ON' 
end

declare @insert varchar(max) = ' INSERT INTO ['+@TableSchema+'].['+@TableName+']'

SELECT @ColumnListing = @ColumnListing+'['+Column_Name+']'+',' 
      FROM  INFORMATION_SCHEMA.COLUMNS c 
      INNER JOIN SYSOBJECTS o 
      ON c.TABLE_NAME = o.name 
      INNER JOIN sys.schemas s 
      ON o.uid = s.schema_id 
      LEFT JOIN sys.all_columns c2 
      ON o.id = c2.object_id 
      AND c.COLUMN_NAME = c2.name

WHERE 
      c.TABLE_NAME = @TableName 
      AND c.TABLE_SCHEMA = @TableSchema 
      AND c2.is_computed = 0 
      AND c.TABLE_SCHEMA = s.name 
ORDER BY c.ORDINAL_POSITION 
set @ColumnListing = SUBSTRING(@ColumnListing,0, len(@columnlisting))

set @insert = @insert+'('+ @columnlisting+')' 
set @query = @query+@insert 
SELECT 
      @ValuesList = @ValuesList+ 
      CASE 
	    WHEN c.COLUMN_NAME = @SkColumnName THEN '-1'
            WHEN DATA_TYPE IN ('INT', 'NUMERIC', 'SMALLINT', 'BIGINT') AND c.COLUMN_NAME NOT LIKE '%DateKey' THEN  '0' 
            WHEN DATA_TYPE IN ('DECIMAL') THEN  '0' 
            WHEN DATA_TYPE IN ('VARCHAR', 'CHAR') AND CHARACTER_MAXIMUM_LENGTH = 1 THEN '''U''' 
            WHEN DATA_TYPE IN ('VARCHAR', 'CHAR') AND CHARACTER_MAXIMUM_LENGTH = 2 THEN '''Un''' 
            WHEN DATA_TYPE IN ('VARCHAR', 'CHAR') AND CHARACTER_MAXIMUM_LENGTH BETWEEN 3 AND 7 THEN '''Unk''' 
            WHEN DATA_TYPE IN ('VARCHAR', 'CHAR') AND CHARACTER_MAXIMUM_LENGTH > 7 THEN '''Unknown''' 
            WHEN DATA_TYPE IN ('NVARCHAR', 'NCHAR') AND CHARACTER_MAXIMUM_LENGTH = 1 THEN '''U''' 
            WHEN DATA_TYPE IN ('NVARCHAR', 'NCHAR') AND CHARACTER_MAXIMUM_LENGTH = 2 THEN '''Un''' 
            WHEN DATA_TYPE IN ('NVARCHAR', 'NCHAR') AND CHARACTER_MAXIMUM_LENGTH BETWEEN 3 AND 7 THEN '''Unk''' 
            WHEN DATA_TYPE IN ('NVARCHAR', 'NCHAR') AND CHARACTER_MAXIMUM_LENGTH > 7 THEN '''Unknown''' 
            WHEN DATA_TYPE IN ('DateTime') THEN '''1900-01-01''' 
            WHEN DATA_TYPE IN ('Date') THEN '''1900-01-01''' 
            WHEN DATA_TYPE IN ('TINYINT') THEN  '0' 
            WHEN DATA_TYPE IN ('FLOAT') THEN  '0' 
            WHEN DATA_TYPE IN ('BIT') THEN  '0' 
            ELSE ''''+DATA_TYPE+'''' 
      END+',' 
      FROM  INFORMATION_SCHEMA.COLUMNS c 
      INNER JOIN SYSOBJECTS o 
      ON c.TABLE_NAME = o.name 
      INNER JOIN sys.schemas s 
      ON o.uid = s.schema_id 
      LEFT JOIN sys.all_columns c2 
      ON o.id = c2.object_id 
      AND c.COLUMN_NAME = c2.name

WHERE 
      c.TABLE_NAME = @TableName 
      AND c.TABLE_SCHEMA = @TableSchema 
      AND c2.is_computed = 0 
      AND c.TABLE_SCHEMA = s.name 
ORDER BY c.ORDINAL_POSITION

set @query = @query+ ' VALUES('+substring(@ValuesList,0,LEN(@valueslist))+')' 

if (@TableName not like '%DimDate%')
begin
	set @query = @query+ ' set identity_insert ['+@TableSchema+'].['+@TableName+'] OFF' 
	set @query = @query+ ' end'
end

if(@Action like 'runit') 
begin 
      exec (@query)
end 
else 
begin 
      print (@query) 
end