Ramblings of a SQL maniac…! 

Twitter LinkedIn

No tweets to display

SSIS Code to Loop Through input columns

public override void Input0_ProcessInputRow(Input0Buffer Row)


// Use Reflection to loop through all the properties of Row:

// Example:

// Row.Field1 (String)

// Row.Field1_IsNull (Boolean)

// Row.Field2 (String)

// Row.Field2_IsNull (Boolean)


foreach (PropertyInfo p in Row.GetType().GetProperties())


// Do something for all string properties: Row.Field1, Row.Field2, etc. That is, ignore the _IsNull properties

if (object.ReferenceEquals(p.PropertyType, typeof(string)))



// If value is null, set it to the token “N/K”

if (p.GetValue(Row, null) == null)


p.SetValue(Row,”N/K”, null);


else //Trim the whitespace


p.SetValue(Row, p.GetValue(Row, null).ToString().Trim(), null);






Use Views for Data Quality

Rather than physically instantiating derived values into DQ columns, build views on top of the data warehouse for this purpose. The BISM model can then be processed off the views.


Using early arriving facts to treat data quality

Poor quality data can be loaded as a late arriving dimension and then the proper value can be derived  into a separate column at a later date.

If loading the BISM cubes off the back of views rather than the DW tables directly, then the DQ transformations can be built into the views and not instantiated as a physical columns.


SSIS to SQL Server Data Type Translations

SSIS Data Type SSIS Expression SQL Server
single-byte signed integer (DT_I1)  
two-byte signed integer (DT_I2) smallint
four-byte signed integer (DT_I4) int
eight-byte signed integer (DT_I8) bigint
single-byte unsigned integer (DT_UI1) tinyint
two-byte unsigned integer (DT_UI2)  
four-byte unsigned integer (DT_UI4)  
eight-byte unsigned integer (DT_UI8)  
float (DT_R4) real
double-precision float (DT_R8) float
string (DT_STR, «length», «code_page») char, varchar
Unicode text stream (DT_WSTR, «length») nchar, nvarchar, sql_variant, xml
date (DT_DATE) date
Boolean (DT_BOOL) bit
numeric (DT_NUMERIC, «precision», «scale») decimal, numeric
decimal (DT_DECIMAL, «scale») decimal
currency (DT_CY) smallmoney, money
unique identifier (DT_GUID) uniqueidentifier
byte stream (DT_BYTES, «length») binary, varbinary, timestamp
database date (DT_DBDATE) date
database time (DT_DBTIME)  
database time with precision (DT_DBTIME2, «scale») time(p)
database timestamp (DT_DBTIMESTAMP) datetime, smalldatetime
database timestamp with precision (DT_DBTIMESTAMP2, «scale») datetime2
database timestamp with timezone (DT_DBTIMESTAMPOFFSET, «scale») datetimeoffset(p)
file timestamp (DT_FILETIME)  
image (DT_IMAGE) image
text stream (DT_TEXT, «code_page») text
Unicode string (DT_NTEXT) ntext