Importing a tab delimited file in SSIS with one field bigger than 255 characters

Question

I am importing a tab delimited file and get this error .

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

Error: 0xC020902A at Task 3 - Data Load for Core Data, Flat File Source [14]: The "output column "Column 85" (448)" failed because truncation occurred, and the truncation row disposition on "output column "Column 85" (448)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. Error: 0xC0202092 at Task 3 - Data Load for Core Data, Flat File Source [14]: An error occurred while processing file "C:\Metrics\report-quoteCoreData.csv" on data row 540. Error: 0xC0047038 at Task 3 - Data Load for Quote Core Data, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (14) 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

When I set truncation error ignore on one of the fields it seems to import .

Unfortunately I get

Column A ( customer ) Column B ( Location ) Column C ( should be Y or N )
Jimmy                        New York               ssssss  ( instead of Y ) 

On this row I have an earlier field which goes over 255 characters and causes the ERROR above in SSIS. If I tell it to ignore the error , I get the wrong data inserted for that row . "Ssss ...." is the field where it goes over 255 characters.

What is the solution here?

Answer 1

I have faced this issue while importing an CSV file with a field containing more than 255 characters, I solved the issue using python.

simply import the CSVin a pandas data frame and then calculate the length of each of those string values per row

then sort the dataframe in descending order. This will enable SSIS to allocate maximum space for that field as it scans the first 3 rows to allocate storage

df = pd.read_csv(f,sheet_name=0,skiprows = 1)
df = df.drop(df.columns[[0]], axis = 1)
df['length'] = df['Item Description'].str.len()
df.sort_values('length', ascending=False, inplace=True)
writer = ExcelWriter('Clean/Cleaned_'+f[5:])
df.to_excel(writer,sheet_name='Billing',index=False)
writer.save()

Answer 2

Within your Flat File Connection Manager, you will need to adjust the OutputColumnWidth property of every column that is not sufficient to hold the incoming values. In your example, Column 85 is currently defined at 255 characters so bump it up to a reasonable value. The goal is to make that value large enough to cover the incoming data but not so large that you're wasting memory space.

Once you change your Connection Manager, any dataflow's that use the same CM will report back that the column definition has changed and you will need to go into them, double click and let the new meta-data trickle down.

I have seen situations where the metadata does not automatically refresh after certain types of transformations (Union All I'm looking at you). As a sanity check, double click on the connector immediately preceding your Destination (probably OLE DB Destination). Click the Metadata tab and ensure Column 85 is 500 or whatever value you assigned. If it's not, then you get to work your way back up the chain to find out where it's stuck. Simplest resolution is usually to delete the troubling transformation and re-add it.