[SSIS][Error]ADO NET Source: Object reference not set to an instance of an object.

Description: System.NullReferenceException: Object reference not set to an instance of an object.  
Description: ADO NET Source failed the pre-execute phase and returned error code 0x80004003.

        突然有天，運行數月的SSIS排程失敗了，這SSIS主要是從MySQL匯入資料到SQL Server上，錯誤訊息如上，用的是MySQL ODBC Provider

        為什會有NullReferenceException的異常讓我百思不解？最後研究發現應該是網路的問題，詢問網管後才得知，公司前一天有被DDOS攻擊，防火牆有做調整，造成大資料量的傳輸會被封鎖，小資料量的傳輸就都沒有問題，DB間使用的網段加入白名單就解了，很瞎吧

     

[SSIS][PostgreSQL]Error code:0x80040E21 Description: Multiple-step OLE DB operation generated errors.

        在SSIS Project裡，我打算從PostgreSQL匯入資料到SQL Server上，然後執行時出現如下錯誤，從錯誤訊息裡得知好像是欄位型態轉換的問題

[OLE DB Destination [29]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E21
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [29]] Error: An error occurred while setting up a binding for the "name" column. The binding status was "DT_NTEXT".
The data flow column type is "DBBINDSTATUS_UNSUPPORTEDCONVERSION".
The conversion from the OLE DB type of "DBTYPE_IUNKNOWN" to the destination column type of "DBTYPE_WVARCHAR" might not be supported by this provider.

        PostgreSQL該欄位上是TEXT，而SQL端對應欄位是NVARCHAR，我中間沒有加入資料轉換元件，對於異質資料轉換，我習慣在來源端就先轉掉好了，比較不容易出問題，所以直接在PostgreSQL來源端用cast轉換就好，例子如下

select id,cast(name as varchar(32)) as name from accounts

         這樣就沒再出錯囉

[SSIS]如何連到PostgreSQL

        首先去下載psqlodbc，因Server是64 bit的，用Visual Studio開發SSIS Project得用32 bit，而我開發佈署都在同一台Server上，所以兩種Driver都得裝喔

        安裝完Driver後，設定ODBC Data Source Administrator，也是32 bit與64 bit都要設，記得名稱要設一樣的，下面會講一下設定

[SSMS][SSIS]Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Access is denied."

        環境是Win 2012 R2與SQL 2014，以管理者身分登入Local端後，用SSMS連SSIS時發生如下錯誤

        Grant Permissions to Integration Services Servic這篇沒用，因為已經是管理者了
     
        結果又是用右鍵Run as administrator執行SSMS就可以解了，UAC好麻煩

[SSIS][MySQL Data Provider]Error: The value was too large to fit in the ADO NET Source.

        因為得從MySQL匯入資料到SQL Server上，參考Connecting to MySQL from SSIS此篇，設好ADO NET Source後，測試一下出現如下錯誤

[SSIS]Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.

Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

        印象中以前用BIDS或SSDT沒看過這警示訊息，但用VS 2013設計SSIS專案時就看到這個警示，解法就如訊息中說的右鍵[以系統管理員的身分]執行，就不會出現囉

[SSIS]OLE DB Source連Oracle的錯誤Cannot retrieve the column code page info from the OLE DB provider.

Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

        就在我按下Preview或是Columns時

無法使用指定的憑證進行解密或加密，可能是因為其中沒有私密金鑰，或是所提供的私密金鑰密碼不正確。

原本我有個預存程序使用憑證簽署過，後來為了加註說明文字得修改預存程序的內容，可是我僅僅只是加上備註而已，預存程序卻不能執行了，好像簽章失效了一般，後來我嘗試再簽署一次卻出現如標題之錯誤，經研究終於找到較佳的解決辦法，說明如下供各位參考，至於我為何會使用憑證簽署可參考如何用預存程序以最小權限原則呼叫遠端SSIS這篇

當你使用簽章簽署預存程序後，使用以下指令，可確認到所有已簽署簽章的模組資訊，此例預存程序為usp_Exectest1，憑證名稱為certTest

SELECT object_name(cp.major_id) '已簽署簽章的模組' ,

   cp.crypt_property ,

   cp.crypt_type ,

   cp.crypt_type_desc ,

   cer.name ,

   cer.pvt_key_encryption_type ,

   cer.pvt_key_encryption_type_desc ,

   cer.subject ,

   cer.expiry_date

    FROM sys.crypt_properties AS cp

    right outer JOIN sys.certificates AS cer

        ON cp.thumbprint = cer.thumbprint

[SSIS]跳出SQLDUMPER.EXE視窗，然後封裝就停止了

        最近在設計SSIS 2005封裝的時候，原本執行都還正常的，突然間不知為什流程就卡在[資料流程工作]，一直呈現黃色狀態，不會結束，如下圖那樣

[SSIS]使用者'User_Name' 的登入失敗

        原本設計的SSIS封裝可手動執行無誤，但是為了讓前端應用程式可呼叫該封裝，我用如何用預存程序以最小權限原則呼叫遠端SSIS的方式來做，結果我手動執行該作業卻出現如下錯誤：

以下列使用者的身分執行: srv001t\test。...32 位元版本 9.00.4035.00  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
已啟動:  上午 10:46:26  
錯誤: 2012-07-05 10:46:30.89     
代碼: 0xC0202009     
來源: IS封裝測試 連接管理員 "SRV001_cm"     
描述: SSIS 錯誤碼 DTS_E_OLEDBERROR。發生 OLE DB 錯誤。
錯誤碼: 0x80040E4D。  有 OLE DB 記錄可用。
來源: "Microsoft SQL Native Client"  
Hresult: 0x80040E4D  
描述: "使用者'SRV001T\test' 的登入失敗。"。  錯誤結束  
錯誤: 2012-07-05 10:46:30.91     
代碼: 0xC020801C     
來源: 資料流程工作 OLE DB來源 [8263]     
描述: SSIS 錯誤碼 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。對 "SRV001_cm" 連接管理員呼叫 AcquireConnection 方法失敗，錯誤碼為 0xC0202009。在此之前可能已公佈過錯誤訊息，說明 AcquireConnection 方法呼叫為何失敗的詳細資訊。  錯誤結束 ...

[SSIS]文字檔裡因換行字元不同所造成的錯誤

        上圖左右各是兩個文字檔，都只有一個長度為10的欄位，左右皆用雙引號包夾，乍看之下真的看不出什分別，但是用同樣的SSIS封裝將文字檔載入資料表，右邊的成功載入，但左邊的卻載入失敗，出現如下錯誤訊息

        [一般檔案來源[253]] 錯誤: 資料轉換失敗。資料行"資料行0" 的資料轉換傳回狀態值4 和狀態文字"文字已截斷，或者一個或多個字元在目標字碼頁裡沒有相符者。"。

錯誤：轉換傳回狀態值 4 和狀態文字[文字已截斷，或者一或多個字元在目標字碼頁裡沒有相符者。]。

　　錯誤: 將資料行 "Latitude" (250) 轉換成資料行 "Latitude" (156) 時，資料轉換失敗。　　轉換傳回狀態值 4 和狀態文字 "文字已截斷，或者一或多個字元在目標字碼頁裡沒有相符者。"。
　　英文的錯誤訊息
The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

　　上面這錯誤，今天在設計SSIS封裝時遇到了兩次，特別紀錄一下解決方法，先說明我的資料來源都是文字檔，資料流程如下圖

SSIS 運算式小抄

　　連SSIS Expression都有Cheat Sheet耶，自己去SSIS Expression Cheat Sheet瞧瞧吧，我覺得這適合初學者及久久用SSIS Expression一次的人參考吧，而且也提供pdf下載呢！可以印出來貼在桌上喔！

　　然後怎麼找到這個呢？剛好我在找如何用SSIS Expression將欄位裡的單個雙引號取代成兩個雙引號時不小心看到的，順便將解法提供給各位參考

REPLACE([COLUMN_NAME],"\"","\"\"")

好工具推薦- BIDS Helper

　　好用的BIDS外掛BIDS Helper ，無意間搜尋到的，可支援SQL Server 2005到SQL Server 2008 R2，對應AS、IS及RS不同的專案會有不同的小功能喔，我覺得有些小功能很方便，像IS的就多了以下幾個我覺得很貼心的功能，推薦給各位

• Deploy SSIS Packages 可以一鍵部署至遠端SQL Server上
• Expression List 條列出所有定義在封裝裡的Expression，不管設在哪的
• Non-Default Properties Report 列出所有非預設值的屬性設定
• Pipeline Component Performance Breakdown 效能調教用
• SSIS Performance Visualization 效能調教用
• Variables Window Extensions 可以輕易複製或搬移變數到另一個範圍

　　 其他功能就等著各位去Documentation挖掘吧

[SSIS]輸出到文字檔，小數值0.123456的開頭0消失了的問題

　　情況是這樣的，我利用SSIS從SQL Server輸出資料到文字檔，再轉給R6用，資料流程中我直接用[OLE DB來源]直接輸出到[一般檔案目的地]，結果卻遇到怪事了，就是來源資料表中有幾個欄位是numeric的格式，這幾個欄位裡面有些值剛好介於0到1之間，假設為0.123456好了，轉換過程中並沒有出錯，但直接開啟輸出後的文字檔卻發現，開頭的0都不見了，變成.123456喔，大於1的值都正常呢，很怪吧
　　網路上有看到有人有一樣的問題，Decimal Formatting from OLEDB Source to Flat File ，但沒有明確的解答，於是我做了一下測試，發現一些奇怪的事，在資料庫引擎端，NUMERIC的功能幾乎等於於DECIMAL，但在Intergration Services，DT_NUMERIC與DT_DECIMAL兩個差異倒是滿大的喔

如何用預存程序以最小權限原則呼叫遠端SSIS

　　最近遇到一個問題，要在ASP網站上呼叫遠端SSIS，以執行轉檔的動作，遠端呼叫SSIS方法有好幾個，我這邊介紹如何用預存程序以最小權限原則呼叫遠端SSIS的方法，因為有安全性的顧慮啊，所以儘可能讓使用的權限愈小愈好囉

權限設到多小呢？我希望只給它執行該預存程序的權限就好，其他讀寫權限都沒有，看看可不可以作到吧

--1.首先請建立一個可以正常執行的作業test1，作業步驟是去執行一個已設計好的SSIS封裝mis99_test，作業的擁有者我先給sa，建立作業的語法如下，建好後，請啟動看看執行是否正常

SSIS新流程轉換元件-Balanced Data Distributor

　　最近在網路上看到有人討論BDD(Balanced Data Distributor)，心裡想說這是什麼啊，查了一下原來是SSIS新的資料流程轉換元件，2011/6/7才增加的，功能似乎是利用多核心的技術來平行處理封裝的資料流，可充分發揮Server上多處理器與多核心的效能喔

　　詳細說明請參考The “Balanced Data Distributor” for SSIS，裡面也有提供下載位址，安裝也很簡單，使用更簡單，根本不用設定，我自己測試的結果，將三百萬筆的文字檔資料轉到資料庫去，使用BDD的確有比較快喔

　　什麼情況適合使用BDD呢?

[BUG]衍生資料行裡的變數竟只會帶初使值？

　　最近有人在無名問我SSIS裡變數的問題，他是用指令碼元件去取得變數，最後將變數塞回到文字檔裡，但結果竟然為0，原以為是對方有地方沒設定好，結果我試了竟然也一樣
　　剛好上胡百敬老師的SSRS的課，順便問了老師這問題，老師幫忙試了也是一樣，看來是衍生資料行這東西搞鬼，不知為什會取得變數的初始值，後來上網查了一下，這篇也有類似的問題，SSIS - user variable used in derived column transform is not available - in some cases，不只我一人遇到，看來應該是Bug啊

SSIS資料類型對應的問題

　　我有一個封裝，會從遠端FTP下載XML檔案到本機，然後用Foreeach迴圈容器列舉下載來的檔案，將完整檔案名稱寫入filename變數，然後將filename傳入當參數對XML檔作一些檢核，檢核成功之後再傳入filename給執行SQL工作(呼叫SP用OPENROWSET)將XML檔案寫回資料庫。

　　原本排程執行都很正常的，突然之間發生錯誤，錯誤擷取訊息如下
  無法大量載入，檔案"c:\file\20110509(X＆X-DDD01).XM" 不存在。

在指令碼元件中寫回ReadWriteVariables中的變數

　　情況是在資料流程中，我有一SQL命令(OLE DB來源)，中間利用多重傳遞，產生三個副本寫到三個不同的資料表ABC(OLE DB目的地)，三個資料表ABC幾乎只有幾個欄位上的差異，一次只會傳遞一筆資料到三個資料表
　　三個目的資料表都有個InsuranceID欄位，但是只有資料表A是設成identity，資料表BC都沒有設，然後每次傳遞三個資料表的InsuranceID都要一樣才行，要不然前端程式會對應不到，所以在SQL命令中已使用( ident_current('資料表A')+1)先取得InsuranceID了，這樣產生的三個副本就都一致啦
　　我想順便取得這次的InsuranceID欄位值，方便我將InsuranceID值Update到另一個資料表去，但是我不想再下一次SQL命令啦，想到說我可以再用一個副本，然後用指令碼元件去接那個欄位，寫入變數供之後使用，這是我想到可以不用再下一次SQL命令做法，不知還有更簡單的方法嗎？

　　問題來了，我想說只要在指令碼元件中寫一行code就行了，結果出現如下錯誤

在 PostExecute 外部無法使用已被鎖定進行讀取和寫入的變數集合。

ReadWriteVariables的設定如下圖