Using the "REPLACENULL" functionality frequently in the "Derived Column" component, the "Conditional Split" component, and other places in SSIS where formulas can be applied is common.
However, I recently encountered an issue with the "DT_DBTIMESTAMP2" data type.
The following formula produced an error:
REPLACENULL(TestDt, (DT_DBTIMESTAMP2,7)"1900-01-01 00:00:00.0000000")
Error: 0xC020902A at Test Transformation, Derived Column [2]: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[TestDt]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This error occurs because "REPLACENULL" returns a "DT_WSTR" data type. To make it work, we need to convert "DT_DBTIMESTAMP2" to "DT_WSTR" and then convert it back to "DT_DBTIMESTAMP2", like this:
(DT_DBTIMESTAMP2,7)REPLACENULL((DT_WSTR,30)TestDt,"1900-01-01 00:00:00")
Alternatively, a more elegant solution is to replace "REPLACENULL" with an "IF" condition:
ISNULL(TestDt)?(DT_DBTIMESTAMP2,7)"1900-01-01 00:00:00":TestDt
