truncation error message

Ok, this is starting to drive me nuts, I've been trying to get this to work for 2 days now. I have a .csv file that I'm reading and importing the data into my table. I defined Derived columns for the type and the lengths as well. I have one column that keeps kicking out this error: The column in the db has a length of 50, the column in my derived column is set to 50, now there are some strings in the colum from the csv file that contain more then 50 characters so I have this in my derived column section: to get the first 50 characters.

(DT_WSTR,50)NAME

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

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

so to some it up. My field (name) in my .csv file can contain 2 to 80 characters but I only want the first 50 and if the field contains greater then 50 characters I'm getting the error above.

Has anyone else run into something like this If so how did you get it working

Hi

Have you tried adding "Derived Column transformation". Worked for me.

In "Derived Column" select Replace '<your column name>'

In Expression - SUBSTRING(<your column name>, 1, 50 )

This should truncate your column to the appropriate length

NB

I have and I still get the error message if there is more then 50 characters in the field.

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

[Flat File Source [1]] Error: The "output column "NAME" (26)" failed because truncation occurred, and the truncation row disposition on "output column "NAME" (26)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

now the length for this field is set to 50 in the derived colum field but I'm still getting the above errors

Yes

I have this;

SUBSTRING(Name,1,50)

Try to use <add as a new column> in your [Derived column], then the length value change to the correct one.

The later in your Data Transformation , ignore your original Name , but use NewName your derived column

Its not with the source, the source is a CSV file and it currently used in production on our SQL 2000 database servers DTS's packages. I'm getting this error now when I migrated the DTS package to SSIS. So if the flat file source was the issue how would this same package (in DTS format) run 100% without errors

The DTS package I'm migrated to SSIS runs nightly on our SQL 2000 DB servers and uses this same file as a source that I'm reading from for my SSIS testing.

IGotyourdotnet wrote:

Its not with the source, the source is a CSV file and it currently used in production on our SQL 2000 database servers DTS's packages. I'm getting this error now when I migrated the DTS package to SSIS. So if the flat file source was the issue how would this same package (in DTS format) run 100% without errors The DTS package I'm migrated to SSIS runs nightly on our SQL 2000 DB servers and uses this same file as a source that I'm reading from for my SSIS testing.

Phil Brammer wrote:

IGotyourdotnet wrote: Its not with the source, the source is a CSV file and it currently used in production on our SQL 2000 database servers DTS's packages. I'm getting this error now when I migrated the DTS package to SSIS. So if the flat file source was the issue how would this same package (in DTS format) run 100% without errors The DTS package I'm migrated to SSIS runs nightly on our SQL 2000 DB servers and uses this same file as a source that I'm reading from for my SSIS testing. And I didn't say it was a bad source file, I said it was a bad "Flat File Source." (Meaning the flat file source component on the data flow)

I misunderstood you, my apologies. So should I create this SSIS package from scratch again I've done it twice so far. What would cause a dataflow 'to go bad'

I have and everything is set to Length = 50, and it still errors out.