Data truncated to 255 characters with Excel ODBC driver [solution]

When importing data from Access, one method is to use Microsoft SQL Server Integration Services or SSIS for short.

When doing so, you add a Source connection, point to Excel, select a Tab, then attach to a Destination connection, perhaps  CSV or Flat file.

When the source connection is created, SSIS reads in the first 8 rows, after the header row, and tries to determine the correct field type.

Let's say you have a text field and the max length of the first 8 rows for a particular column is 35 characters.  It sets the field type to string, with a max length of 255 by default.

But what if your actual max length of the column is 1500.  Well, SSIS will throw an error, indicating the field will be truncated and will give a big red x.

So one option is to go into the advanced editor, and resize the field length.  But guess what, it reverts right back, because it's reading the first 8 rows.

So how do you get around this behavior?  Well for a one time load, you can fake it out by adding a dummy row of 1500 characters and it reads that row and sizes the field accordingly. 

But if you have a recurring job, that's not going to work.  It turns out, there's a setting in the Registry that you can modify.

In RegEdit --> HKEY_LOCAL_MACHINE --> Software --> Wow6432Node --> Microsoft --> Jet --> 4.0 --> Engines --> Excel --> TypeGuessRows (Reg_DWORD), change it from 8 to 0 (zero):

Save, exit RegEdit, restart your IDE and it should pick up the actual field size accordingly, then modify your destination and good to go.  You will need to apply the same change to the production and test servers as well.  And be careful, messing with the Registry is playing with fire sometimes, so take your time and be precise with your changes.

Here's a link from Microsoft to assist.

And there you have it~!