Today I needed to import some data from an ODBC data source into a SQL Server 2005 database, so I used the new import tool in 2005 (right click on database in SQL Server Management Studio and choose Tasks - Import Data...). I had imported data from the same ODBC data source into SQL Server 2000 databases, so I thought it would just work when trying to import into SQL Server 2005.
I knew I was running into problems when I looked at the SQL for the column mappings: the columns of type string had mapped to nvarchar(1) -- that's right, an array of 1 nvarchar. This seemed odd to me, so I used the array length values that I had used when previously importing into SQL Server 2000 databases. However, when I tried to run the import package, I received error messages during the Pre-execute step:
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error 0xc004701a: Data Flow Task: component "Destination - Query" (40) failed the pre-execute phase and returned error code 0xC0202025.
Well, as usual, an error message that is not very helpful. However, after poking around in Google, I came upon some entries that talked about using the new max keyword for array lengths. I decided to try it out; I looked at the autogenerated SQL for the import column mapping, and everywhere I saw nvarchar(1) I put in nvarchar(max), and sure enough it worked.
So, if you ever run into this obscure error when importing into SQL Server 2005, see if any of the column mappings use nvarchar(1) in the SQL. If so, try using nvarchar(max) instead.