Home / server / Questions / 102243
Accepted
user155695
Asked: 2010-01-14 04:00:59 +0800 CST

SQL Server; how to import data from another DB with different collation; conversion errors?

  • 772

I have 2 databases setup on a server. I want to import data from DB1 to DB2, using the SSIS wizard (right click DB > Tasks > Import Data)

  1. DB1 is the source DB, it has a collation set to Cyrillic_General_CI_AS
  2. DB2 is the destiantion DB, it has a collation of SQL_Latin1_General_CP1_CI_AS

I can run through the wizard, using a query of "select * from myTable". That creates the table on my destination DB no problem, I don't save this.

I run the wizard a second time, so I can go into the mappings and select "delete existing rows" (since I can't do this first time round). Then at the end I save it, save it on the SQL server.

I get the following errors : 

"MyColumn" cannot be processed because more than one code page (1251 and 1252) are specified for it.
 (SQL Server Import and Export Wizard)

I have checked on MSDN and found this article suggesting that difference in collation types could cause some issues with data type conversions

It was suggested to use nvarchars, so I have started again making sure the table on my destination DB are using nvarchars, but I'm now getting the following error

Data Flow Task: The product level is insufficient for component "Data Conversion 1" (197).

Changing my destination DB collation to cyrillic is not an option, since later on I will be importing from other DBs so may run back into this issue.

Is there anything I can do to work around this issue? I hope I have provided enough info

Thanks

sql-server-2005 sql ssis

1 Answers

  1. Best Answer

    I dont know much about importing different collation types but have you tried exporting the table into say a .csv file then once that is complete import it into the destineation table.

    Not the most efficient way to do it but may be a work around.

    • 1