June 10, 2011 at 6:22 am
Hello All,
We are using SSIS 2008 in combination with SQL Server 2008.
The ETL makes an import from a csv file. A data conversion set the data to Unicode string [DT_WSTR].
In order to import the data the table must have the same collation (Latin1_General_CI_AS) and character set.
But SQL Server has a default setting of iso_1 for the character set.
This info is retreived via INFORMATION_SCHEMA.COLUMNS
Can somebody help me how to change the character set?
The following queries I tried without succes.
ALTER TABLE table CHANGE field Varchar(20) CHARACTER SET utf8;
ALTER TABLE table MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8
Thanks in advance....
Edwin
June 10, 2011 at 7:08 am
Hi Edwin,
You need to change the column definition to be NVARCHAR to support unicode. Here's a script to show you how:
USE Test
GO
/* Create Test table with non-unicode column */
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Test
END
CREATE TABLE dbo.Test (Col1 VARCHAR(20))
GO
SELECT character_set_name,collation_name FROM information_schema.columns WHERE table_name = 'test'
/* Change to support unicode and ensure collation */
ALTER TABLE dbo.test ALTER COLUMN col1 NVARCHAR(20) COLLATE Latin1_General_CI_AS
GO
SELECT character_set_name,collation_name FROM information_schema.columns WHERE table_name = 'test'
GO
Hope that helps
Leyton
June 10, 2011 at 7:15 am
Yes,Just convert Column varchar to nvarchar.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply