2015年5月18日 星期一

[SSIS] 透過SSIS將有難字的資料轉出後內碼會被改變(亂碼)

問題:透過SSIS轉成文字檔(或是匯入資料庫)時難字的內碼會被改變,例如正確的應該是,但經過SSIS後會變成

原因:
        難字的內碼不在BIG5 code page裡面,也就是造字,當SSIS透過Microsoft OLE DB provider連線到oracle會判斷Big5 950(non-UNICODE),就會直接報錯不讓你轉。但當SSIS透過Oracle OLE DB provider連線到oracle時則會判斷為UNICODE,此時就會將CH_NM欄位轉換成UNICODE,這個動作是由Oracle OLE DB provider自動判斷並自動轉換的,此時難字字元內碼就已經被轉換過了,所以存進SQL Server後的binary也不會相同了。

解法:
        簡單來說就是將Oracle來源端的難字欄位轉成binary(透過utl_raw packages),並直接寫入到SQL Server對應的binary欄位其內碼就不會變,由於binary是一樣的這時再將此欄位用convert的函數再轉回char。

1. 在SQL Server上建立一個 B2U_01的暫存TABLE,將有難字的欄位NAME其type設定為binary
2. SSIS部分會建立兩個資料流程,一個是Oracle資料匯入SQL Server,一個是SQL Server資料匯出成文字檔
    2.1 在第一個資料流程(Oracle資料匯入SQL Server)的來源端
    原語法: select NAME from myORCL.CARD_HOLDER_DIM where CH_ID='A123456789';
    修正語法:select utl_raw.cast_to_raw(NAME) from myORCL.CARD_HOLDER_DIM where CH_ID='A123456789';

    2.2 在第二個資料流程(SQL Server資料匯出成文字檔)的來源端
           語法:SELECT convert(char(40),[NAME]) as NAME FROM [B2U_01]