Danny63


Hi, i'm new to SSIS and trying to import some csv files (comma delimited) into SQL Server. A NULL value for a CHAR column is correctly regonized as NULl in SQL Server, but a NULL value for of a mapping to a VARCHAR column in SQL Server is not recognized correctly and i get the value "'NULL'" in SQL Server (including the single comma.

Sample:

CSV file contains columns A and B. A and B contains the Text NULL.

Column A is mapped to a CHAR field, and column B is mapped to a VARCHAR field in SQL Server.

After the import, SQL has the following value: A = NULL as NULL, B 'NULL' as text.

did anyone else had this problem

thanks so much for any help.




Re: SSIS Import of CSV don't recognize NULL values for VARCHAR fields

Anthony Martin


Try creating a derived column like this:

LEN(TRIM(<field>)) > 0 <field> : NULL







Re: SSIS Import of CSV don't recognize NULL values for VARCHAR fields

Rafael Salas

You could use a derived column transformation w/replace for column B; use an expression to replace the string 'NULL' with with nulls:

[Column B]=="NULL" NULL(DT_WSTR, <length>) : [Column B]

* Replace <Lenght> for the actual lenght value







Re: SSIS Import of CSV don't recognize NULL values for VARCHAR fields

Danny Meyer

LEN... doesnt work because during transformation it already has the text 'NULL', so it will be not zero length.

Thanks anyway, that leaded me to the right way to look for further, and i got it worked using FINDSTRING(Column,"NULL",1) > 0 NULL(DT_WSTR,100) : Column.

The last solution [Column B]=="NULL" NULL(DT_WSTR, <length>) : [Column B] looks even more simple. I try that as well.

Thanks so much your help