Answered by:

loading a CSV file with an SSIS Flat File Source Task

Question

I am trying to load a csv file into a table using an SSIS flat file source task.

However, I am getting an error because one of the the columns, definition, which is mainly string has numeric values included.

I have set the property of the column (definition) as string [DT_STR] 8000.

This is the error that I keep getting:

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

I also tried redirecting the data into a table but kept on getting this error:

"Error: 0xC02020A1 at Data Flow Task, Flat File Source [242]: Data conversion failed. The data conversion for column "Definition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

It seems that your CSV file is not very well formatted. The included Flat File Source doesn't do a stellar job of handling poorly formatted files.

What you can do:- You can configure the Flat File Source to redirect errors to an error output, where you can then examine the rows in the file that are causing problems. This may supply you with enough information to determine a root cause for the bad data, perhaps enough to allow the system that's supplying the file to be fixed.- You can use the Delimited File Reader Source or Delimited File Source instead. They are much more tolerant of poorly formatted files.

I am currently using SSIS package 2005, therefore, I can't use the Delimited File Reader Source or Delimited File Source as they are built for SSIS 2008.

So to remove the unwanted carriage returns in the notes column, I would have to use a Script Task and .Net Code. The problem is that I am not much of a coder. Is there a sample script that I can use to resolve the problem.

Right-click on the Flat File Source and choose Advanced Editor from the submenu.

Once in the Advanced Editor for your Flat File Source...

On the "Input and Output Properties" tab

Expand the "Flat File Source Output" in the "Inputs and outputs" window.

Then, what you want to do, select each of your External Columns and compare the Length to the same column under Output Columns. Make the Output Column length for each, match the External Column length. (Make sure your DataType is the same too
while you are at it.)

When this is done and you get back to your Data Flow, if you have any downstream sorts, it might give you the metadata mismatch message -- when it asks you if you want it to automatically fix it, choose Yes.

Also, if you find that you are still getting that error after doing the above, check your file values and see if there is any values in the field erring that is larger than the length you have defined. If it is, you'll need to adjust both the Output
Column length of the field and External Column length of the field to match.