4

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:

  1. configuration trickery within the flat file connection and/or flat file source,
  2. derived column (I need help with the derivation),
  3. script component.
Hadi
  • 36,233
  • 13
  • 65
  • 124
Scott
  • 169
  • 1
  • 3
  • 14

1 Answers1

1

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
Chris Mack
  • 5,148
  • 2
  • 12
  • 29
  • This will throw an exception for the value `9999-12-31 23:59:59.0000000` provided by the OP, you have to add more conditions, or you can use a script component to achieve this. – Hadi Nov 16 '17 at 20:17
  • Ok then there you go +1 – Hadi Nov 16 '17 at 20:30
  • In what sense? The essence of it is that once it's in your file as "NULL", it's no longer `NULL`, it's a string. The expression will take a string, and convert it to either a `DT_DBTIMESTAMP`/`DT_DBTIMESTAMP2` or a `NULL`, depending on whether or the string is "NULL". Once it's passed through the transformation, the derived column will have a data type of `DT_DBTIMESTAMP`/`DT_DBTIMESTAMP2` – Chris Mack Nov 19 '17 at 23:06
  • *but return the data back as a character string* - no, the derived column will have a data type of `DT_DBTIMESTAMP`/`DT_DBTIMESTAMP2`. The expression `NULL(DT_DBTIMESTAMP)` is essentially saying `NULL` of type `DT_DBTIMESTAMP`. – Chris Mack Nov 19 '17 at 23:13
  • Thanks this works, but I want to fully understand the processing... I've mapped the columns as DT_STR in the flat file advanced tab. The expressions use the casts (i.e. DT_DBTIMESTAMP2,7) to validate the character string, *but return the data back as a character string* (???) They are fed as character strings to the table as character strings, and the database engine implicitly converts the character strings to DATETIME/DATETIME2??? And if I wanted SSDT to do the conversion, I need to assign the expressions to new variables, and use these to load the table? Is this correct? – Scott Nov 19 '17 at 23:22