none
Truncation Error

Answers

  • Hi Steve, one approach that can be quite sensible, if you do not know the files schema, is to pump a file into a table. (For this purpose, the columns can be as large as you like) and then run the data profiling task against that table. Then, you will be able to see exactly how long the columns are, along with avaerage width, percentage of NULL values, and a ton of other metrics. This will help you to make better desicions. Then, in your dataflow, use an error path on your flat file source, and set the columns to re-direct on truncation, so that you can quarentine any rows that have massively long columns, and take a desision on them manually.
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Monday, January 31, 2011 5:05 AM

All replies

  • I think ssis scan first 200 rows of flat file to determine datatypes and lengths.

    But you can go to advanced tab of flat file connection manager and change them as you want.


    http://www.rad.pasfu.com
    Sunday, January 30, 2011 10:02 AM
  • It only does this if you select "Suggest Data Types", otherwise it stes everything to String (50). If you have volitile data within the flat file, the best thing is to set the data types yourself manually on the connection maanger. (I'm actually doing that myself at this very moment on my other monitor!)
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Sunday, January 30, 2011 10:05 AM
  • Thanks for the replies Reza, Peter.  To me it seems like deciding on the width of a column is not a particularly exact science. For example, I often deal with "Description" fields that contain free-flow text. This type of field invariably throws up a truncation error,  for obvious reasons. So I generally take a guess and plug in a width of 250 width string. If that doesn't work, I generally just pick width that significantly over estimates the potential - say 500. It  seems a bit haphazard. I would of thought there might be a more scientific solution, considering that this type of approach would leads to storage inefficiencies.

    Cheers

     

    Steve 

    Monday, January 31, 2011 3:59 AM
  • Hi Steve, one approach that can be quite sensible, if you do not know the files schema, is to pump a file into a table. (For this purpose, the columns can be as large as you like) and then run the data profiling task against that table. Then, you will be able to see exactly how long the columns are, along with avaerage width, percentage of NULL values, and a ton of other metrics. This will help you to make better desicions. Then, in your dataflow, use an error path on your flat file source, and set the columns to re-direct on truncation, so that you can quarentine any rows that have massively long columns, and take a desision on them manually.
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Monday, January 31, 2011 5:05 AM