I am using sql server 2016. Is this expected behaviour? The reason, I am asking this because we have some ETL process which has views which selects data from staging tables and load into dimensions. Video Ad includes various creative visual elements which attract consumers with more effectiveness in advertising method. The staging table columns can have blank spaces and if the dimension has the column datatype defined as INT or numeric datatype, then it might fail with datatype conversion error. So, we have decided to put a CASE statement and replace '' with NULLs.
