Как преобразовать строку "NULL" в плоском файле в DATETIME или DATETIME2?

Question

tl; dr: Как преобразовать "NULL" в плоском файле в NULL в SSDT/SSIS?

В SSMS запустите этот код:

/* 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

Сконфигурируйте SSMS для создания результатов в файл в формате CSV с заголовками: Инструменты -> Параметры -> Результаты запроса -> SQL Server -> Результаты в текст: раскрывающийся список с разделителями-запятыми, установите флажок Включить заголовки столбцов в набор результатов.

Ваш вывод будет похож на:

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

В SSDT настройте соединение с плоским файлом, вкладку "Дополнительно", установите для типов столбцов DT_DBTIMESTAMP для DT, DT_DBTIMESTAMP2 для DT2.

(Дополнительный вопрос: есть ли идея, почему SSDT неизменно ошибается и устанавливает оба столбца в DT_DATE? Очевидно, что столбцы имеют компонент времени.)

В источнике плоских файлов настройте RetainNulls = True (хотя я думаю, что это не имеет значения, поскольку у меня нет истинных нулей, то есть пустой столбец, не содержащий данных.

Как получить SSDT для создания тех же результатов в #target, что и в SSMS? Итак, мне нужно преобразовать текст "NULL" в "правильный" NULL в цели.

Мои предпочтения:

1. хитрость конфигурации в соединении плоского файла и / или источника плоского файла,
2. производный столбец (мне нужна помощь с выводом),
3. компонент сценария.

Answer 1

Вам нужно будет вставить столбцы в ваш источник плоских файлов в виде строк (например, [DT_STR]), а затем преобразовать их, используя преобразование "Производный столбец" со следующими выражениями:

Выражение для DT:

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

Выражение для DT2:

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