使用SSIS进行ETL的过程中,你可能会遇到数据库排序规则冲突的问题,这个时候就需要使用Data Flow中的组件Data Conversion. 我们常用的是将Unicode字符转换为NonUnique字符,因为简体中文Chinese_PRC_CI_AS对为NonUnique. 这样就可以将字符进行转换,最终写到目的表中。
那么除了这样的方式,最容易想到的就是在数据库创建表的时候就已经对排序规则进行了定义。对于数据库排序规则的语法如下:
CREATE TABLE [dbo].[Stg_Daily_Vist](
[Date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Time] [varchar](5) COLLATE Chinese_PRC_CI_AS NULL,
[SelectPeriod] [int] NULL
) ON [PRIMARY]
这样就可以在Data Flow Task 中直接将Source File的字段导入目的表。
SQL Server中的中英文排序规则:
Chinese (Hong Kong) | 0x20c04 | Chinese_Hong_Kong_Stroke_90_CI_AS | 950 |
Chinese (Hong Kong S.A.R.) | 0xc04 | Chinese_Hong_Kong_Stroke_90_CI_AS | 950 |
Chinese (Macau S.A.R.) | 0x1404 | Chinese_PRC_90_CI_AS | 950 |
Chinese (PRC) | 0x804 | Chinese_PRC_CI_AS | 936 |
Chinese (PRC) | 0x20804 | Chinese_PRC_Stroke_CI_AS | 936 |
Chinese (Singapore) | 0x1004 | Chinese_PRC_90_CI_AS | 936 |
Chinese (Taiwan) | 0x404 | Chinese_Taiwan_Stroke_CI_AS | 950 |
Chinese (Taiwan) | 0x30404 | Chinese_Taiwan_Bobomofo_CI_AS | 950 |
English (Australia) | 0xc09 | Latin1_General_CI_AS | 1252 |
English (Belize) | 0x2809 | Latin1_General_CI_AS | 1252 |
English (Canada) | 0x1009 | Latin1_General_CI_AS | 1252 |
English (Caribbean) | 0x2409 | Latin1_General_CI_AS | 1252 |
English (Hong Kong S.A.R.) | 0x3c09 | Latin1_General_CI_AS | 1252 |
English (India) | 0x4009 | Latin1_General_CI_AS | 1252 |
English (Indonesia) | 0x3809 | Latin1_General_CI_AS | 1252 |
English (Ireland) | 0x1809 | Latin1_General_CI_AS | 1252 |
English (Jamaica) | 0x2009 | Latin1_General_CI_AS | 1252 |
English (Malaysia) | 0x4409 | Latin1_General_CI_AS | 1252 |
English (New Zealand) | 0x1409 | Latin1_General_CI_AS | 1252 |
English (Philippines) | 0x3409 | Latin1_General_CI_AS | 1252 |
English (Singapore) | 0x4809 | Latin1_General_CI_AS | 1252 |
English (South Africa) | 0x1c09 | Latin1_General_CI_AS | 1252 |
English (Trinidad) | 0x2c09 | Latin1_General_CI_AS | 1252 |
English (United Kingdom) | 0x809 | Latin1_General_CI_AS | 1252 |
English (United States) | 0x409 | SQL_Latin1_General_CP1_CI_AS | 1252 |
English (Zimbabwe) | 0x3009 | Latin1_General_CI_AS | 1252 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14321372/viewspace-567449/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14321372/viewspace-567449/