[This article was contributed by the SQL Azure team.]
Now that you have decided to move your SQL Server data to SQL Azure, it is a good time to change your varchar(max) and text data types to nvarchar(max). Changing your data types to Unicode will make your migration from SQL Server to SQL Azure easier when using SQL Server Integration Services (SSIS).
When migrating from SQL Server to SQL Azure using SSIS you need to use two different data sources, the SQL Server Native Client 10.0 data source to connect to SQL Server and the .NET Framework Data Provider for SqlServer data source to connect to SQL Azure. When using these data types, SSIS transforms your non-Unicode data types (varchar and text) to Unicode and back again, which works fine if the length of the data in the column is shorter than 8000 characters. However, if the text is longer than 8000 characters you will get an error like this:
Error 0xc0204016: SSIS.Pipeline: The "output column "State_Text" (168)" has a length that is not valid. The length must be between 0 and 8000. (SQL Server Import and Export Wizard)
If you modify your source and destination columns that are varchar(max) and text to nvarchar(max) SSIS will not have to transform your strings between the two provides and the columns will move successfully.
Here is a single table in non-unicode:
CREATE TABLE [State] (State_Id int, State_Text varchar(max))
Here is some example Transact SQL that will change the column type.
ALTER TABLE [State] ADD [Temp] nvarchar(max) NULL GO UPDATE [State] SET [State].[Temp] = [State_Text] ALTER TABLE [State] DROP COLUMN [State_Text] EXECUTE sp_RENAME '[State].[Temp]' , 'State_Text', 'COLUMN'
In this example, I have a table called State and a column called State_Text that is data type text.
In case you didn’t know, you should make all your columns Unicode for better localization support, and this post presented another reason to make the change. Do it before you run the SQL Server Export/Import Wizard and the process of moving your SQL Server into the cloud will be much easier