To homepage To homepage
Solved

Truncation SSIS error

  • 2 March 2018
  • 1 reply
  • 182 views
D
Rank

Hi,

We are trying to read a csv file and truncating some columns because our table would be too large otherwise. Unfortunately deploy and execute fails with this 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."."

We are pretty sure it isn't an encoding problem because when we increase the column size ("Length") in "Edit multiple text files"->"Columns" we can read the data just fine so the error must occur because of the truncation.

Does anybody know what options we have to use to let TimeXtender know that we are ok with the truncation of the data in the columns?

Thanks,

David

icon

Best answer by JTreadwell 2 March 2018, 20:14

View original

1 reply

JTreadwell
Rank
Userlevel 3
Badge +5

Hi David, 

This is an SSIS error, SSIS does not allow you to implicitly truncate data in this way because of a potential, inadvertent, loss of data. 

Instead, you will need to expand the data type to allow for all the data to be transferred in the initial staging or ODX database. Then you can apply a transformation in the staging area to truncate this data when loading it into the valid table. 

To do this:

  1. Update your data type in the text file data source to ensure the character size is large enough to handle all the values contained in your source. 
  2. In staging right click on the field you wish to truncate > Field Transformations
  3. In the transformation pane on the right, select "Custom" from the drop-down list and click Add. 
  4. Use the SQL operator LEFT([FieldName], [Text length]) to only include the first [desired number] of characters in the final valid table. Should look something like this:

     

  5. Deploy & Execute. 

Hope this helps. Please let me know if you need additional information. 

-Joseph Treadwell

Reply


Terms and ConditionsCookie settings