Data conversion Failed on MS SQL Import
The database was upgraded to MS SQL 9 and required me to use 2005 SQL Server Management Studio instead of Enterprise Manager. While trying to import a .csv file, I ran into a number of errors and spent awhile trying to figure out what the problem was. If you get a similar error, the problems seems (I say seem, because I am not a database guy and am simply guessing. Further exacerbating the problem is my extremely limited use of MS SQL) to be that the import tool isn’t allocating enough room for the strings going into certain columns. I.e it looks at the first 100 rows to determine how much space a typical field needs, and if a value goes way over that, it truncates the string and errors. This is the error message I got:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column “ClickThroughURLValue” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
(SQL Server Import and Export Wizard)
I tried to alter the values in the destination table, but that didn’t help at all. It turns out; you need to alter the OutputColumnWidth on the first screen. Since space and performance weren’t an issue for me, I just made the nvarchar quadruple the size it needed to be, so it could accommodate the random large string. One of the problems with the tool is it will error and fail on the insert when it encounters too big of a value, rather than just truncating and logging. This doesn’t seem like a huge issue, until you have a 15 gig .csv with over 4 million rows and it errors after 2 hours of importing… Below is a screenshot showing where the change needs to be made. Doing this will also alter the size of the column in the destination table and prevented the error for me.
