tl;dr: How to I convert "NULL" in a flat file to a NULL in SSDT/SSIS?
In SSMS run this code:
/* Run once
SET NOCOUNT ON
CREATE TABLE #source (DT DATETIME, DT2 DATETIME2)
CREATE TABLE #target (DT DATETIME, DT2 DATETIME2)
INSERT INTO #source
VALUES (GETDATE(),GETDATE()),(NULL,NULL),(GETDATE(),'9999-12-31 23:59:59')
*/
-- Cntl-Shift-F, results to CSV with headers
SELECT * FROM #source
-- Cntl-D, results to grid
INSERT INTO #target
SELECT * FROM #source
SELECT * FROM #target
-- Prep for next run
TRUNCATE TABLE #target
Configure SSMS to create results to file as CSV with headers: Tools --> Options --> Query Results --> SQL Server --> Results to Text: Comma delimited pulldown, check Include column headers in the results set.
Your output will be similar to:
DT,DT2
2017-11-16 10:09:31.997,2017-11-16 10:09:31.9970000
NULL,NULL
2017-11-16 10:09:31.997,9999-12-31 23:59:59.0000000
In SSDT, configure a flat file connection, Advanced "tab", set the column types to DT_DBTIMESTAMP for DT, DT_DBTIMESTAMP2 for DT2.
(Side question: any idea why SSDT invariably gets this wrong, and sets both columns to DT_DATE? Clearly the columns have a time component.)
In the flat file source, configure RetainNulls = True (although I think this is irrelevant since I don't have true nulls, i.e. blank column containing no data.
How do I get SSDT to create the same results in #target that I get in SSMS? IOW, I need to convert the text "NULL" to a "proper" NULL in the target.
My preferences:
You'll need to pull the columns into your Flat File Source as strings (e.g., [DT_STR]) and then convert them using a Derived Column transformation with the following expressions:
Expression for DT:
DT == "NULL" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP) DT
Expression for DT2:
DT2 == "NULL" ? NULL(DT_DBTIMESTAMP2, 7) : (DT_DBTIMESTAMP2, 7) DT2