3

I am building an SSIS package to load a flat file(CSV) to SQL server.

I am getting the below error when I try to insert into the column state_code (char(2)) to the SQL server. If I change the destination datatype to Char(4), the package executes successfully.

I suspect this is the problem created due to the "NULL" in the CSV. The package is taking NULL as 4 characters and trying to insert into Destination(Char(2)).I googled a lot. But I didn't find any similar situation anywhere.

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Column_1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Hadi
  • 36,233
  • 13
  • 65
  • 124
Damodara Lanka
  • 51
  • 2
  • 9
  • 5
    Within a CSV file, if the cell contains the word "NULL", then it's value is the 4 character string "NULL". A blank cell would be interpreted as the value `NULL`. "NULL" and `NULL` are not the same. – Thom A Dec 21 '17 at 15:18
  • You can use the [derived column task](https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/derived-column-transformation). Place between your source and destination. Use it to replace the string null with a null value. – David Rushton Dec 21 '17 at 15:21
  • To add to what @Larnu said, usually it's caused when you save from Excel as Excel tends to replace the actual `NULL` fields with the text `"NULL"` – Jacob H Dec 21 '17 at 15:22
  • Thanks guys, Can you tell me the expression to do that in Derived column transform – Damodara Lanka Dec 21 '17 at 15:26
  • Hey Jacob. That is a CSV file. For you ref type of file: Microsoft Excel Comma Separated Values File (.csv – Damodara Lanka Dec 21 '17 at 15:29
  • Assuming that you are using `DT_WSTR` then something along the lines of: `[Column Name] == "NULL" ? NULL(DT_WSTR,2) : [Column Name]`. P.s. csv stands only for "Command Separated File" (no Microsoft involvement). Also, I would guess the OP has the strings "NULL" in the csv as someone has copied the data out of SSMS and not removed the value "NULL" using a Find & Replace. – Thom A Dec 21 '17 at 15:31
  • There is a good solution provided. why not marking it as accepted? – Yahfoufi Jan 05 '18 at 13:09

2 Answers2

3

Just add a derived column transformation with the following expression to replace the "NULL" string with a NULL value:

UPPER([Column]) == "NULL" ? NULL(DT_WSTR,2) : [Column]

or you can replace it with an empty string

UPPER([Column]) == "NULL" ? "" : [Column]

UPDATE 1

based on your comments i posted this update

NULL() function take the data type as parameter, so if you are working with Date columns you can use NULL(DT_DATE) Read more.

For Date column you can use this expression:

UPPER([Column]) == "NULL" ? NULL(DT_DATE) : [Column]

For integer column you can use this expression:

UPPER([Column]) == "NULL" ? NULL(DT_I4) : [Column]
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

When you are working to export your data from Database to Flat file then you cant handle the null values of data type INT. Refer to this link where which data types handle the null values: Microsoft SQL Docs: NULL (SSIS Expression)

ejderuby
  • 710
  • 5
  • 21
satya deep
  • 1
  • 2