透過 SSIS 存取 Excel 欄位超過 8 筆紀錄後由數值轉文字的 NULL 錯誤

以下是 SSIS 2005,但 MSN 的 Blog 改類別名稱真麻煩…咬牙切齒

感謝永豐金的朋友解了我好久以來的問題,參考如下的網址

http://blog.miniasp.com/post/2008/08/How-to-read-Excel-file-using-OleDb-correctly.aspx
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

例如 Excel 內的資料如下:

image

若要 SSIS 能正確讀到文字的部分,而非 NULL,需要:

修改 Registry,預設 8,代表讀 8 筆紀錄判別該欄型別,設 0 代表要讀完全部才判讀型別:

image

Registry 改完後,立刻生效。就可以利用以下的 VBScript 測一下

Dim cnn
Set cnn=CreateObject(“ADODB.Connection")
cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Book1.xls;Extended Properties=""EXCEL 8.0;HDR=NO;IMEX=1″";"
cnn.Open
Dim rec
Set rec=cnn.Execute(“SELECT * FROM [sheet1$]")
Dim strVar
With rec
While Not .Eof
If IsNull(rec.Fields(0).Value) Then
strVar = strVar + vbCrLf + “NULL"
Else
strVar=strVar + vbCrLf + CStr(rec.Fields(0).Value)
End If
.MoveNext
WEnd
End With
cnn.Close
MsgBox strVar

在 SSIS 加入 OLE DB 連結,先隨便設定後,手動透過屬性視窗修改整個 ConnectionString 的定義,最重要的是 IMEX=1 要設上去

ExcelAccess

再加入 OLE DB Source,照一般的 Excel 查詢即可

image

可以利用進階編輯再確定一下 OLE DB Source 的 Output 欄位輸出內容是否為 WSTR

image

再次感謝這位朋友解了我 2~3 年的困擾  微笑

感謝巧玫提供了另一種查詢方式

SELECT *
FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;HDR=Yes;IMEX=1;Database=D:\SSIS\EXTData_Reuters\ReutersData\Rating_Stock.xls’
, ‘ SELECT * FROM [S&P(L_CR&FUTR_For)$b2:x10] ‘) WHERE F2 IS NOT NULL
SELECT * FROM [S&P(L_CR&FUTR_For)$b2:X10]

大寫X 可指定撈回所有筆數

11 Comments

  1. Bryant Lee
    Posted 2011 年 06 月 03 日 at 08:46:20 | Permalink | 回覆

    胡老師請教一下,我在SSIS 2005裡的"指令碼工作"把從DB查詢出的值放到一個公用變數(varibles),再從"洐生的資料行"中把這個變數寫到DB或文字檔,都能順利完成,但我改用"指令碼元件",在程式運行過程中監看,確定資料有寫入該變數裡,但將該變數拉進"洐生的資料行"寫入DB或文字檔,結果卻都為0或空白,請教胡老師是否是我遺漏了那個動作,謝謝您的解答!!

  2. Bryant Lee
    Posted 2011 年 06 月 07 日 at 06:26:03 | Permalink | 回覆

    胡老師您好,之前的提問我的資訊不夠完整。我作了一個簡單測試加上截圖(抱歉圖沒有鏈結,請複製到IE觀看),或許胡老師您可以幫我看出問題所在(我試了好多次了都不行…)

    1.http://i12.photobucket.com/albums/a239/kb0705/Variables00.jpg 是我宣告的變數。

    2.http://i12.photobucket.com/albums/a239/kb0705/Variables01.jpg 我在OLE DB來源去下一個很簡單的count()語法(Query出來值是15),我想把計數值丟到dtsCNT變數中。

    3.http://i12.photobucket.com/albums/a239/kb0705/Variables02.jpg 是我在指令碼元件中指定readwritevariables為dtsCNT。

    4.http://i12.photobucket.com/albums/a239/kb0705/Variables03.jpg 是指令碼元件中編輯指令碼的code。我有將dtsCNT的值show出來,值是15,確定是有寫入。

    5.http://i12.photobucket.com/albums/a239/kb0705/Variables04.jpg 是設定衍生資料行的部份,我將自訂變數dtsCNT和資料行CNT拉進來,並輸出到文字檔來比較。

    6.http://i12.photobucket.com/albums/a239/kb0705/Variables05.jpg 輸出的結果,dtsCNT數值仍是0,但資料行CNT是正確的,可見變數的值並沒有成功取得。

  3. Posted 2011 年 06 月 07 日 at 12:32:09 | Permalink | 回覆

    沒看到指令碼元件內,賦予輸出資料行內容的程式碼,不要將輸入跟輸出都叫 CNT
    若輸出資料行叫 OutputCNT
    應該有 Row.OutputCNT=Row.CNT

    • Bryant Lee
      Posted 2011 年 06 月 08 日 at 03:20:15 | Permalink | 回覆

      胡老師您好,請問如果沒有設置輸出資料行會有影響嗎?我的指令碼元件(轉換)的確是沒有設定輸出資料行,因為我的目的只是單純利用指令碼元件將select count(*) 的結果放到一個名叫dtsCNT的variables裡,再將他拉到衍生的資料行裡輸出至檔案中。我研究過網路上的討論,以我的case來說,都是說將dtsCNT在指令碼元件中設定為ReadWriteVariables,並撰寫如下的code:
      Public Class ScriptMain
      Inherits UserComponent

      Dim mCNT As Integer

      Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
      mCNT = Row.CNT
      End Sub

      Public Overrides Sub PostExecute()
      Me.ReadWriteVariables(“dtsCNT").Value = mCNT
      MyBase.PostExecute()
      End Sub
      End Class

      難道說指令碼元件的ReadWriteVariables只能在指令碼元件中的自訂腳本中使用,無法將其內容值賦予封裝的變數供整個封裝使用?天啊~~~這個問題困擾我好幾天了,真是快瘋了….
      再次thank for your time….

  4. Bryant Lee
    Posted 2011 年 06 月 08 日 at 05:35:55 | Permalink | 回覆

    另外,之前發表的第5個jpg中文字檔的第一個數值是dtsCNT,第二個數值是直接拉資料行的CNT,和您看到的第4個jpg檔裡衍生的資料行上下順序是相反的……我再整理了一下截圖,希望可以表達得更清楚..
    http://i12.photobucket.com/albums/a239/kb0705/variables06.jpg –>把變數拉進衍生的資料行
    http://i12.photobucket.com/albums/a239/kb0705/variables07.jpg –>設定輸出文字檔對應的欄位,第一個欄位輸出為資料行CNT,第二個欄位輸出為變數dtsCNT
    http://i12.photobucket.com/albums/a239/kb0705/variables08.jpg –>可以看見資料行CNT資料正確的輸出(15),但dtsCNT卻是0….但我確實在上述的code 裡把它寫入dtsCNT了….@@

    • Posted 2011 年 06 月 10 日 at 01:51:42 | Permalink | 回覆

      這我也碰到過,但似乎是多執行緒同時執行,先後不一定設定/取到變數內容。會有有時有;有時沒有的狀況。但若在資料流程元件外部,之後透過控制流程再讀就會讀到。

      • Bryant Lee
        Posted 2011 年 06 月 17 日 at 02:21:42 | Permalink

        胡老師感謝您的解答,果然我在另一個控制流程就能成功將它寫到目的資料庫了!!
        再次謝謝您!!

  5. rice
    Posted 2011 年 06 月 16 日 at 02:56:55 | Permalink | 回覆

    胡老師您好:打擾您了~ 請教您一個SQL SERVER SSIS的小問題,我們公司採用SQL SERVER 2005,在封裝組態設定以往都是用XML檔案,因為有加密的需求。
    改為組態設定時,將組態類型選擇為SQL SERVER,此時在資料庫中建立一個組態資料表[dbo].[SSIS Configurations],勾選將連線管理員的資訊存入到
    這個資料表中,密碼在Table中的顯示資訊為********,再將封裝部署到SERVER上後排程執行後都會出現資料庫連線失敗的訊息,
    如果將組態資料表[dbo].[SSIS Configurations]之密碼改為明碼就可以執行(資料庫連線不會失敗)。
    但是為了安全問題無法將密碼改為明碼,有加密的需求,本以為SSIS已經幫我們將密碼加密存入組態資料表[dbo].[SSIS Configurations],
    但卻無法讀取********,是否有能夠讀取之方式或是我們漏了甚麼設定?? 還是說SSIS沒有密碼加密且sa可讀取執行的功能,非得要自己
    另外寫CODE自行加解密不可??
    感謝您~ 感恩^^

  6. Posted 2011 年 06 月 16 日 at 13:13:23 | Permalink | 回覆

    SSIS 沒有加密,就是存 “*" 這個符號,你可能要自己搭配 Script Task 寫 code 加解密後改 Connection 屬性

    • rice
      Posted 2011 年 06 月 17 日 at 01:45:52 | Permalink | 回覆

      了解,感謝老師~

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: