KB-SSIS Moving SQL NVarchar to ORACLE NVarchar2

之前用SSIS在做ORACLE與SQL間資料搬移時，最痛恨遇到NVarchar跟Unicode Code問題了。

明明是NVarchar(SQL)搬到NVarchar2(ORACLE)，兩邊都Support Unicode，但SSIS都老會要求指定Data Flow Destination的Code Page，接著又會嚷著SQL中得到的Unicode不能直接轉成Non-Unicode，所以就得很可笑地在Nvarchar與NVarchar2兩個Unicode欄位中加上一個Data Conversion。如下圖:

不過，苦難還沒有結束... 如果很不幸地，NVarchar中還真的放了Unicode字元，則在轉換時，會出現以下的錯誤訊息。

Error: 0xC02020C5 at Data Flow Task Failure Sample, Data Conversion [543]: Data conversion failed while converting column "UnicodeField" (505) to column "UnicodeFieldMapping" (557). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task Failure Sample, Data Conversion [543]: The "output column "UnicodeFieldMapping" (557)" failed because truncation occurred, and the truncation row disposition on "output column "UnicodeFieldMapping" (557)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

這問題讓我很困擾，不過因為遇到時都是做些一次性的資料搬移，所以我都很鄉愿地繞路解決(最愛用的一招是回去改用SQL 2000 DTS，很奇怪，用DTS搬資料時，幾乎不曾為Enocding傷過腦筋)，未曾認真與它對決過。

最近同事想用SSIS來做重要的日常資料搬移，就卡在這個問題上，由於搬移是每天要跑的，繞路的成本就會高出許多，這回我總算被迫要收服這隻妖怪。東試西試之餘，忽然想到之前使用Query Express的經驗，Oracle的OLE DB Driver才能正確顯示Unicode，Microsft的OLE DB for Oracle反而不行，所以我試著把Data Flow Destination的OLE DB換成Oracle版。

美妙的事出現了!! 使用Oracle Provider for OLE DB後，連Data Conversion都不用了，直接對Unicode對Unicode，一次搞定。

看一下令人興奮的結果，SQL裡的六頭牛被搬到ORACLE裡了，NVarchar --> NVarchar2，萬牛奔騰的感覺豈是一個爽字可以形容，哈!!

Keyword: SSIS, SQL Server Integration Service, Oracle, NVarchar2, Unicode

歡迎推文分享:

Published 17 May 2007 12:14 PM 由 Jeffrey

Filed under: 中文編碼, SQL 2005, KB

意見

# jaceju said on 16 May, 2007 09:59 PM

喔喔喔~~這招必學~~後面有可能會用到 Oracle 說~~

每次遇到資料庫字元編碼問題就很頭痛捏...

# jdli said on 07 September, 2007 02:47 AM

我看著你的牛，正在微笑，真是太寶貴了，不過換了Driver ，又有一個問題出現，那就是 data type，使用 oracle driever for OLE DB 時，如果 datatype 為 number ( oracle side)，則在匯入到 SQL Server 2005 時會有問題 (這個問題如使用 Microsoft OLE DB For Oracle 時可以被解決，怎麼這麼兩難呢?)，不知道前輩您遇過沒?

# Jeffrey said on 07 September, 2007 10:15 AM

我測試了一下，從Oracle轉了NUMBER(18,0)到SQL 2005的DECIMAL(18,0)，過程很順利，沒有遇到錯誤。不知你有問題的狀況為何?

# jdli said on 10 September, 2007 01:03 AM

I am back. 如果是number(18) 沒問題，但是如果是 Number, 就會有問題

# Jeffrey said on 11 September, 2007 06:32 AM

To jdli:

我試了一下，設NUMBER時，的確就會產生一個"The output column "COLNAME" has a precision that is not valid. The precision must be between 1 and 38"的訊息。查了一下，恭喜老爺、賀喜夫人，似乎是SSIS的Bug:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282497

目前要避開的方法是指定NUMBER的Precision，看看可不可行。

# brian said on 22 October, 2007 08:02 PM

請問大大，我的ssis provide怎麼都找不到

Oracle Provider for OLE DB? ><，有要額外安裝什麼嗎？

# Jeffrey said on 23 October, 2007 09:47 AM

To Brian, 要額外安裝Oracle寫的OLE DB Driver。

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html

# fly said on 26 December, 2008 07:21 PM

大大:

裝了ORACLE OLD DB Driver後,原本微軟的OLD DB for ORACLE就沒辦法用了。

# Jeffrey said on 28 December, 2008 07:46 PM

to fly, 我接觸的大部分機器都是兩種Driver並存，而且可以任意選一種來用(一個很有名的例子還是要選對版本才看到得到Unicode)，不知你遇到的錯誤為何?

# FLY said on 31 December, 2008 03:55 AM

JEFFREY:

dtswiz.exe-找不到元件

【這個應用程式無法啟動，因為找不到core40.dll，重新安裝應用程式可能可以解決這個問題。】我裝的是oracle release出來的檔案ODAC101040.exe。應該是10g

# Jeffrey said on 31 December, 2008 09:49 AM

to FLY, 我猜是PATH路徑設定問題，ORACLE Client挺依賴它的，請你檢查ORACLE相關安裝路徑下有沒有core40.dll這個檔案，若有，該路徑有沒有在PATH的環境參數中，而依據MS的一篇KB，support.microsoft.com/.../en-us，這個檔案還不准在其他目錄下出現。Verify that the Ociw32.dll file is in the Oracle_Root\bin folder. This .dll file cannot exist at any other location on the client computer. Make sure that the Oracle Client Component DLLs (for example, the Core40.dll file and the Ora*.dll file) do not exist outside the Oracle_Root folder or subfolders.

# FLY said on 05 January, 2009 10:28 PM

To Jeffrey:

謝謝你，問題解決了，是路徑問題。

# FLY said on 03 February, 2009 10:54 PM

Jeffrey:

請問可以把您網站的文章，link到我的blog嗎?謝謝!!

# Jeffrey said on 03 February, 2009 11:29 PM

to FLY, 歡迎引用，請註明原始出處及連結即可。

# coco said on 05 October, 2012 12:20 AM

我是反過來要從oracle轉至SQL

遇到NVarchar2轉至SQL長度會變成兩倍長。我是用SSMA轉的

你的看法呢?

標題 (必要的)
姓名 (必要的)
你的URL (選擇性的)
意見 (必要的)
Remember Me?
(提醒: 因快取機制，您的留言幾分鐘後才會顯示在網站，請耐心稍候)
CAPTCHA / 驗證碼 : 5 + 3 =

KB-SSIS Moving SQL NVarchar to ORACLE NVarchar2

意見

你的看法呢?

搜尋

這個部落格

Syndication