1

I have an Data Flow step in an SSIS package that simply reads data from a CSV with 180 or so columns and inserts it into a MS SQL Server table.

It works.

However, there's a CSV file with 110,000+ and it fails. In the Output window in Visual Studio there a message that says:

The data conversion for column "Address_L2" returned status value 2 and status text "The value could not be converted because of a potential loss of data.

In the Flat File Connection Manager Editor, the data type for the column is string [DT_STR] 50. TextQualified is True.

The SQL column with the same name is a varchar(100).

Anyhow, in the Flat File Source Editor I set all Truncation errors to be ignored, so I don't think this has to do with truncation.

My problem is identifying the "offending" data.

In the same Output window it says:

... SSIS.Pipeline: "Staging Table" wrote 93217 rows.

I looked at row 93218 and a few before and after (Notepad++, Excel, SQL) and nothing caught my attention.

So I went ahead and removed rows from the CSV file up to what I thought was the offending row and when I tried the process again I got the same error, but when I look at the last entry that was actually inserted into the SQL table it doesn't match the last, or close to the last rows in the CSV file.

Is it because it doesn't necessarily insert them in the same order?

In any case, how do I know what the actual issue is, especially with a file this size that you can't go through it manually?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Amarundo
  • 2,357
  • 15
  • 50
  • 68

2 Answers2

0

You can simply change the length of the column in the flat file connection manager to meet the destination table specifications. Just open the flat file connection manager, go to the Advanced tab and change the column length.

Note that you can select multiple columns and change the data type and length at once

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

You could add an Error output to the SSIS component which is causing the error (not sure from your question whether it's the flat file source or the Staging Table destination).

Hook up the Error output to "nowhere" (I use the Konesans Trash Destination), activate a data viewer on it, and select just the problem column (along with any thing which helps you identify the row) into the data viewer. Run in Visual Studio, and you'll see which rows are failing.

SebTHU
  • 1,385
  • 2
  • 11
  • 22