如何从SSIS中的文本文件中以NULL值读取列，而不需要用随机的东西替换它

Its a simple task, and I have tried a few things which aren't working, I am going to lay down all the trials here.

这是一个简单的任务，我已经尝试了一些没用的东西，我将在这里列出所有的尝试。

TASK: Take data from a text file delimited by ',' and move into an existing empty table in SQL Server.

任务:从以'，'分隔的文本文件中获取数据，并移动到SQL Server中现有的空表中。

My source destination has the option "Retain null values...." checked.
我的源目的地选择“保留null值....”检查。
There is no mismatch of data type between source and destination columns
源列和目标列之间没有不匹配的数据类型
I also have "Keep Nulls" option checked in my destination table.
我还在目标表中检查了“Keep Nulls”选项。

Here's the contents of the first text file

这是第一个文本文件的内容

 1002,"Murphy","Diane","x5800","dmurphy@classicmodelcars.com","1",NULL,"President"
 1056,"Patterson","Mary","x4611","mpatterso@classicmodelcars.com","1",1002,"VP Sales"
 1076,"Firrelli","Jeff","x9273","jfirrelli@classicmodelcars.com","1",1002,"VP Marketing"
 1088,"Patterson","William","x4871","wpatterson@classicmodelcars.com","6",1056,"Sales Manager (JAPAN, APAC)"
 1102,"Bondur","Gerard","x5408","athompson@classicmodelcars.com","4",1056,"Sale Manager (EMEA)"

The first row has a null value in the second last column, and this first row is the cause of error. Its the old error message,

第一行在第二列中有一个空值，第一行是错误的原因。这是旧的错误信息，

[Orders Text [70]] Error: Data conversion failed. The data conversion for column "ShippedDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Similar thing happens with another text file where the Shippeddate has a null value.

类似的事情发生在另一个文本文件中，其中Shippeddate具有空值。

I understand that since I am reading from a text file, it reads the NULL as text and not just NULL. So I can use the derived column and use the replace function in string to replace it with another value.

我理解，因为我正在从一个文本文件中读取，所以它将NULL作为文本而不是NULL。所以我可以使用派生列并用字符串中的replace函数替换另一个值。

Also in the case of dates which look like this

还有像这样的日期

           2003/1/13 0:00:00

I can't even replace them with a fictional date. It still throws me an error after reading it as string, replacing using derived function and then converting using data conversion function.

我甚至不能用一个虚构的约会来取代它们。当我把它读成字符串，用派生函数替换，然后用数据转换函数转换后，它仍然会抛出一个错误。

My question is "How to replace these values as NULL and not the string NULL in various datatypes in sql server"?

我的问题是“如何将这些值替换为NULL，而不是sql server中各种数据类型中的字符串NULL”?

Also if possible, What do I do such that it reads NULL as NULL and not string. I want it to be NULL and not replace it with some random value or 0. Its important that the missing data be shown as missing.

如果可能的话，我应该做什么呢?它将NULL读取为NULL，而不是字符串。我希望它是空的，而不是用随机值或0替换它。重要的是要将丢失的数据显示为缺失数据。

1 个解决方案

#1

Is far as I know NULL (empty) value from TXT source files (using Flat File Connection Manager) is only where no value is present.

据我所知，TXT源文件(使用平面文件连接管理器)的NULL(空)值仅在不存在值的地方。

So your record:

所以你的记录:

1002,"Murphy","Diane","x5800","dmurphy@classicmodelcars.com","1",NULL,"President"

... should be like this

…应该是这样

1002,"Murphy","Diane","x5800","dmurphy@classicmodelcars.com","1",,"President"

... to have NULL (empty) value.

…具有NULL(空)值。

I think the best way is to import everything as STRING, then REPLACE "NULL" values with NULL (empty) and then format everithing as needed in destination DB.

我认为最好的方法是将所有内容都作为字符串导入，然后用NULL(空)替换“NULL”值，然后根据需要在目标DB中格式化所有内容。

For NULL-ing your data you could use add new column on Derived Column transformation:

为使数据无效，可以在派生列转换中添加新列:

ColX == "NULL" ? NULL(DT_WSTR, 50) : ColX

For dates also import as STRING, and with SUBSTRING (and FINDSTRING) expressions build the right format for converting into desired DATETIME format.

对于日期也作为字符串导入，使用子字符串(和FINDSTRING)表达式构建正确的格式，以便转换为所需的DATETIME格式。

#1