SSIS 連接管理員呼叫 AcquireConnection 方法失敗

[Oracle xxxx_sc [1]] 錯誤: SSIS 錯誤碼 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。
對 "testOracle" 連接管理員呼叫 AcquireConnection 方法失敗，錯誤碼為 0xC0202009。
在此之前可能已公佈過錯誤訊息，說明 AcquireConnection 方法呼叫為何失敗的詳細資訊。

在工具列，專案下點選封裝名稱屬性，進入後，在組態屬性下的偵錯中，將Run64bitRunTime改為False即可。如下圖:

需要執行物件，才能進行這項操作

人客的環境最近更新的informix ODBC的版本,由原來的3.11換成3.70 TC4。
在做DTS資料轉入時出現了以下的錯誤

步驟錯誤來源: Microsoft Data Transformation Services (DTS) Package
步驟錯誤描述:需要執行物件，才能進行這項操作
 (Microsoft Data Transformation Services (DTS) Package (80040005): 提供者產生的程式碼發生執行例外:  EXCEPTION_ACCESS_VIOLATION)
步驟錯誤碼: 80040005

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Need to run the object to perform this operation
(Microsoft Data Transformation Services (DTS) Package (80040005): Need to run the object to perform this operation
) (Microsoft Data Transformation Services (DTS) Data Pump (80040005): Code execution exception: EXCEPTION_ACCESS_VIOLATION)
Step Error code: 80040005

一如往常,這個錯誤訊息令人丈二金剛摸不著頭腦,用中文google半天，找不出什麼蛛絲馬跡可以測。換成英文去查，也都和遇到的狀況不太相同。

後來直接VNC過去，看到ODBC的設定才發現原來他的機器中，相同名稱的DSN，一個放在【使用者資料來源名稱】下使用的是舊版的DRIVER，另一個放在【系統資料來源名稱】下使用的是新版的DRIVER。

新舊版本同時存在沒什麼問題，但DSN名稱相同，我想這就讓DTS搞混了吧。


移除掉舊版的DSN，DTS就正常囉。

SSIS dtexec 參數指令

dtsexec /?
command line 列出常用的選項

選項不區分大小寫。連字號 (-) 可以用來取代斜線 (/)。
/Conf[igFile]       Filespec (把同一個project會用到的設定都維護在.dtsConfig)
/De[crypt]          Password (package設EncryptSensitiveWithPassword,執行時再指定解密密碼)
/F[ile]             Filespec 
/Set                PropertyPath;Value


my command line:

DTEXEC /FILE "C:\Test.dtsx" /DECRYPT "test2010" /CONFIGFILE "C:\test.dtsConfig"   /SET "\package.variables[gsGuid].Value";"54A2A383-28A6-4227-AA9F-21A8BDFEB853" /SET "\package.variables[gsType].Value";"typeI"




其中,gsGuid是用來寫入sql server uniqueidentifier欄位, 試了一下, 參數前後加大括弧也OK


DTEXEC /FILE "C:\Test.dtsx" /DECRYPT "test2010" /CONFIGFILE "C:\test.dtsConfig"   /SET "\package.variables[gsGuid].Value";"{54A2A383-28A6-4227-AA9F-21A8BDFEB853}" /SET "\package.variables[gsType].Value";"typeI"

不過.....如果這個gsGuid值,要同時給不同DB使用, 則在傳入時,就不要傳入有加大括弧的參數值,以免非SQL SERVER DB資料庫寫入時,發生截斷的問題....因為長度就會超過varchar(36)了.

SSIS 字串轉型成GUID

在SSIS dataflow task 的 drived column  ,將字串轉型為GUID時,前後要加大括弧

(DT_GUID)("{" + @[User::gsGuid] + "}")

變數的資料型態

多加寫了一段union的sql,該sql總共有6個欄位,
用一個foreach loop task來接收這個sql result set中每一筆的欄位值
結果,重新執行package後, 在loop里的script task出現"長度超過"的錯誤

逐一debug後,才發現新加的union sql中,某個欄位是一個計算欄位, 剛好, 符合的筆數都是由新增的union 中的sql所取得

原本在ssis是用一個int16的變數去接收, 由於sql 中,該計算欄位應是回傳integer型態,所以造成script task 中用到這個變數值時,己經是讀取到不正確的數值

後來在sql中,將該欄位轉型為smallint後,重跑package後就ok了.

因此,最好要確認union select 回傳的型態和ssis variable的型態要對應正確

Excel openrowset 奇怪的問題(續)

人客家的SSIS,一段時間後都會出現以下的錯誤.
剛開始一頭霧水,以為是上傳的EXCEL檔有問題,
後來發現,原本在SSMS使用正常的excel openrowset, 在同時間也無法使用.

檢查了EXCEL檔案內容,檢查了openrowset的語法, 都沒更動過也沒寫錯...怪哉.


所有的錯誤都出現在Data Flow Component 的OLEDB Source, 里頭放的是一段excel openrowset的語法:

SSIS 錯誤碼 DTS_E_OLEDBERROR。發生 OLE DB 錯誤。錯誤碼: 0x80040E14。
有 OLE DB 記錄可用。來源: "Microsoft SQL Native Client" Hresult: 0x80040E14 描述: "無法初始化連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 的資料來源物件。"。
有 OLE DB 記錄可用。來源: "Microsoft SQL Native Client" Hresult: 0x80040E14 描述: "連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.Jet.OLEDB.4.0" 報告了錯誤。提供者並未給予任何關於錯誤的資訊。"。

"元件 "Excel OpenRowset" (1230)" 驗證失敗，傳回驗證狀態 "VS_ISBROKEN"。

一或多個元件驗證失敗

工作驗證期間發生錯誤。

載入封裝檔案 "D:\SSIS\subImport.dtsx" 時發生錯誤 0xC0012050。ExecutePackage 工作發生封裝驗證失敗。無法執行封裝。


最後,什麼都沒做,重新啟動SQL Server Services, 又回復正常.

這次,我依照之前遇到的怪問題的解法方法,
在機碼
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 下新增一個名稱
DisallowAdHocAccess = 0

重新啟動SQL Server ,再觀察一段時間看看吧

不支援關鍵字: 'provider'

想要在SSIS中呼叫自訂的DLL,其中需要動態傳入connection string,以方便在不同的環境中使用

於是在script task中,利用讀取dtsConfig中的connectionString,再傳遞給自訂的DLL method

執行時,在script task出現了一段錯誤

 System.ArgumentException: 不支援關鍵字: 'provider'。  
 於 System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable
 synonyms, Boolean firstKey)  
 於 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)  
 於 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)   
 於 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)   
 於 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)   
 於 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)  
 於 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)  
 於 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Initialize(IDataServices dataServices, Object connection)   
 於 System.Data.Linq.DataContext.Init(Object connection, MappingSource mapping)   
 於 System.Data.Linq.DataContext..ctor(String fileOrServerOrConnection, MappingSource mapping)   

於是乎,將dtsConfig中connectionstring的provider移除, 接著又出現了以下的錯誤

"ConnectionString 中未指定 OLE DB 提供者。正確範例為 Provider=SQLOLEDB；。"

 於是乎,又將dtsConfig中connectionstring的Provider改成 Provider=SQLOLEDB

怎麼著..., 又出現剛剛的 不支援關鍵字: 'provider'。  的錯誤...

也過給多種不同的連線字串,也爬了很久很久的文, 攏無法度解決....

唉, 同樣的連線字串,在不同的地方使用, 有的OK, 有的出搥... 

最後, 試了半天,只好先在自訂的DLL,把接收過來的連線字串中,其中Provider的關鍵字後的字串全移除掉

所以原本傳入

Data Source=ttt;User ID=sa;Password=1234;Initial Catalog=test;Provider=SQLNCLI.1;Persist Security Info=True;

取代後就變成

 Data Source=ttt;User ID=sa;Password=1234;Initial Catalog=test;

然後,我的SSIS就可以正常執行了.

方法不好,只能解燃眉之急, 還得要Provider=SQLNCLI.1;Persist Security Info=True;這段寫在最後面才行

後記.....

同事在問, 你很奇怪耶, 既然要移掉Provider, 那何不在傳入時就不要給不就行了??


我也很想啊, 就是不行啊,一直出現
 "ConnectionString 中未指定 OLE DB 提供者。正確範例為 Provider=SQLOLEDB；。"

到底connectionString 要怎麼給呢?

SQL Agent 執行 SSIS Package

先建立一個 Credential
1.先新增一個本機的使用者帳號,例如ssisRunner
2.在sql server management studio下, 展開Security,Credential按右鍵,New Credential,在Identity輸入步驟1建立的使用者帳號

再建立一個Proxies
1.在sql server management studio下,展開SQL Server Agent 下的Proxies,在SSIS Package Execution按右鍵New Proxy,新建一個Proxy
2.在Active to the following  subsystems下,勾選 SQL Server Integration Package

新增job
1.在sql server management studio下,SQL Server Agent Job新增一個工作,
1.在step 中,Type選SQL Server Integration Services Package,
2.Run as指定為前述建立的ssisRunner
3.在General頁籤,Package Source 選擇File System, 然後輸入Package 所在的位址

完成後, 這樣就可以透過SQL Agent來執行package囉

email html task

前輩都說, 輪子不要重覆造
想要一個ssis email html 內容的task,
在codeplex上找到toddmcdermid寫的 task "Send HTML Mail Task"

有ssis2005及ssis2008的版本

我安裝了ssis2005, 裝完後, 在toolbox加入這個control flow task
點選編輯或f4時,出現以下這個錯誤

TITLE: Microsoft Visual Studio
------------------------------

The task with the name "Send HTML Mail Task" and the creation name "ToddMcDermid.SSIS.SendHTMLMailTask, SendHTMLMailTask90, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6c005605b216cf47" is not registered for use on this computer.
Contact Information:
Send HTML Mail Task

------------------------------
BUTTONS:

OK
------------------------------

看來是沒有註冊正確
利用GACUtil -i "C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\SendHTMLMailTask90.dll"

要重開機嗎?
看來是要, 重開後...再編輯....ok

測試......body中文有問題.
加了 charset=big5 也沒用,看來要好好研究一下原作者的source code 了..
殘念........輪子是破的

回歸簡單,最後直接用 SmtpClient 自已寫吧, code很多, google 一下就有了

在SSIS 使用自訂的DLL方法

1.開發的dll需要簽署
2.複製dll到 C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents(此步驟可以省略)
3.複製dll到 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
4.拖曳dll到 C:\WINDOWS\assembly
5.SSIS中在 Script Task 加入dll參考

如何共用單一個 dtsConfig

多個package要共用同一個dtsConfig時,如果package的參數不是共通的,就要分開設定不同的dtsCOnfig.
但如果只想要共用一個,這樣比較好維護呢?

Step 1: 首先,先設好一個完整且含有每個package會用到的參數dtsConfig檔

Step 2: 然後view code by 每個.dtsx , 在code中手動加入一個Configuration tag

＜dts:configuration＞＜dts:property name="ConfigurationType"＞1＜/DTS:Property＞＜dts:property name="ConfigurationString"＞c:\test\CRIMBIS.dtsConfig＜/DTS:Property＞＜dts:property name="ConfigurationVariable"＞＜/DTS:Property＞＜dts:property name="ObjectName"＞TestConfig＜/DTS:Property＞＜dts:property name="DTSID"＞{E54F62C7-C936-4E2E-903C-0694706E7C96}＜/DTS:Property＞＜dts:property name="Description"＞＜/DTS:Property＞＜dts:property name="CreationName"＞＜/DTS:Property＞＜/DTS:Configuration＞

Step3: 將package 的屬性 SupressConfigurationWarnings 設為 True.這樣當這個dtsConfig中的參數未出現在這個package時,開啟package就不會有錯誤跑出來了

來自 dtsConfig Sharing - Suppress Configuration Warnings

里面有很多ssis 的小撇步,推