How to convert the string "NULL" to a flat file in DATETIME or DATETIME2?

Question

tl; dr : How to convert "NULL" in a flat file to 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 for CSV with headers: Tools → Options → Query Results → SQL Server → Results for Text: Disabled with comma-separated values, check Enable column headers in the result set.

Your output will look like:

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, an extended 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.)

RetainNulls = True ( , , null, .. , .

SSDT #target, SSMS? IOW, "NULL" "" NULL .

:

• / ,
• ( ),
• script.

Answer 1

(, [DT_STR]), Derived Column :

DT:

DT == "NULL" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP) DT

DT2:

DT2 == "NULL" ? NULL(DT_DBTIMESTAMP2, 7) : (DT_DBTIMESTAMP2, 7) DT2