SSIS Error

  • I have a dataflow process where I am attempting to import a text file do some basic data conversion add columns and insert into my DB. I am getting an error on the dataflow step where I am bringing in the data via the text file. Most of the file loads and then I get a conversion error which reads like this:

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

    I have had a simular error when moving data from a DB table to another DB Table and I resolved that by using trimming on the troubled field [via LTRIM(RTRIM(My_Field))].

    Is there a way to do this on a text file import without having to import into a DB table and extracting with appropriate T-SQ

  • Mark, I might misunderstood here.....But u say u r using dataflow, where u r loading from flat file to sql table. U have the same function of triming in teh dataflow. Also, when you open yout destination and go to error page there is a option where you can say On truncation Ignore faliure.

    thsi should work......

  • Hi gyanendra

    I tried what you said with the truncation ignore but I still got the same error. How do you trim when using the flat file source object?

    Appreciate the help.

  • just bring the derived column and use the build in trim function in expression............

  • I tried the derived column suggestion but the failure actually takes place in the insert of the text file in the flat file source object. Most of the file is inserted and processed but it fails in the latter part of the insert from the source.

    I have checked the data length and type via the connection object and tried changing it to a larger size but this doesn't seem to resolve the issue either. I have also tried adding a new connection manager but it failed in the same location.

  • mark, now i am confused and back to zero. please describe the steps in your ssis and thinsg you trying to do. it might be a issue of datatypes and columns that are empty, especially when it is datatime.........

    what is teh datatype of column that give syou the problme?. if it is possible attach here the sample flat file with few amount of data on it

    thanks......

  • Hi Gyanendra,

    I appreciate your perserverance. What I have done is in the configure Error option of the flat file source object I have set the offending fields to ignore the failure on error and truncation. This allows the object to load the values up do the data conversions & the derived data and eventually put into the DB table. Not the best way to do things I know but from what I can see there isn't anything wrong with the data except for some nulls. Which could be the source of the problem. If that is all it is this isn't a problem for me.

    This is a fairly large ssis package with several child packages of which this one is where the error is taking place. My goal is to gain understanding of the existing process before beginning any major changes related to the business.

    Appreciate the help I think your suggestion of making changes to the error configuration was very helpful.

    Thanks --

  • SO , U r right it is not th ebest way of doing things. But if everything gets loaded with this option then u good. I think the problem here is the value where it is NULL. Basically I think the value is not NULL ,there is some empty string like --

    ' ' so its trying to convert this value to something and giving you truncation error. WHat is teh datatype for this column on DB? I belive it is datetime...becoz it gives a lot of problems when you do insertion.

    You can stiil solve the issue with built in function for NULL types in th ederived columnsssss...

    I hope it goes well with you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply