Nrthugu

I'm using SSIS 2012 and SQL Server 2014. I've had this issue forever and have YET to see a viable solution - and I'm not sure why more people don't experience this issue. Here is my simple data flow task:

I receive numerous Excel files from clients. The problem is that some of the fields contain text that is greater than 255 characters - and as we all know Microsoft is NOT smart enough to read all rows (the first 8 records are read and assumes that if the first 8 rows are less than 255 characters then ALL the rows must be less than 255).

Of course this leads to truncation errors. Even if I set my SQL destination column value to nvarchar(max), SSIS will still error out. As I'm sure many have experienced simply using SSMS and importing an Excel file manually will generate this error. For my SSIS package, I have a data conversion task that supposedly converts the source data to a format that can then be brought into SQL Server. Apparently not.

Here is my simple data flow task with the offending column highlighted:

So, the limited solutions I've found online recommend sorting the data so that the widest text value is at the top so SQL Server can read it. I can't do this for hundreds of files each and every time. And I need the data in the original sort order, too, so that's not viable. Or I should insert a dummy record as the first row in the Excel file.

And how would this be done - in a script task? Again, over potentially hundreds of files? I've also heard I can change the registry somehow. Do I want to mess with that? Do any of these sound practical?

I have not seen anything online that's comparable to my situation - and I'm flummoxed. How can that be? I can't be the only one who experiences these types of truncation issues.

Anyway, I appreciate any and all help.

Potential solutions:

1- Change the sample size by updating TypeGuessRows in the registry key for HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel. Path might not be exactly same in your machine but would be similar.

2- Use Flat File Connection Manager instead of Excel Connection Manager, right click, go to Advanced Editor, go to Input and Output Properties tab, find your column on the left under Output Columns and set its length to 1000.

As a result of my own frustration with SSMS's inadequate diagnosis of data types in imported data, I wrote a tool to do the job instead. It works better for me; it might work better for you, if you're open to scripting solutions outside of the Microsoft environment. The tool is a Python script, available at https://pypi.org/project/execsql/. The IMPORT command will read an entire Excel or CSV file to determine data types, pretty much guaranteeing that the data will be imported successfully. Import from CSV is much faster than import from Excel, and line breaks inside columns of the CSV file are handled correctly.