SSISのトランザクションの設定がうまく出来ない

質問

パッケージ全体が成功すると
パッケージ全体をコミットさせる、といった事をしたいのですが、
どうしてもうまく動作してくれません。。

ちなみに、以下の設定を実施しました。

・パッケージ全体の[TransactionOption]を[Required]
・各タスクの[TransactionOption]を[Supported]
・パッケージ全体の[ProtectionLevel]を[DontSaveSensitive]

＃もちろん、[TransactionOption]を全て[Supported]にすると、
＃問題無く動作します。

以下、エラーメッセージを纏めたものです。
--------------------------------------------------
開始: XX:XX:XX
エラー: 2008-08-XX XX:XX:XX
コード: 0xC001A004
説明: 保持されている接続で互換性のないトランザクション コンテキストが指定されました。
この接続は別のトランザクション コンテキストで確立されています。
保持されている接続は 1 つのトランザクション コンテキストのみで使用できます。
エラー終了
--------------------------------------------------
エラー: エラー: 2008-08-XX XX:XX:XX
コード: 0xC020801C
ソース: フラットファイルよりTestTableへインポート
OLE DB 変換先：TestTable [283]
説明: SSIS エラー コード DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER。
エラー コード 0xC001A004 により、接続マネージャ "TestDB" に対する
AcquireConnection メソッドの呼び出しが失敗しました。
このエラーの前に、AcquireConnection メソッドの呼び出しが失敗した理由の詳細が記載された
エラー メッセージが報告されている可能性があります。
エラー終了
--------------------------------------------------
エラー: 2008-08-XX XX:XX:XX
コード: 0xC0047017
ソース: フラットファイルよりTestTableへインポート
DTS.Pipeline
説明: コンポーネント "OLE DB 変換先：TestTable" (283) が検証に失敗し、
エラー コード 0xC020801C が返されました。
エラー終了
--------------------------------------------------

このエラーが発生した際に、
「分散トランザクションを中止しています。」と、
パッケージの進行状況に表示されていることから、
分散トランザクション自体は動作しているように見えます。

以上より、
どなたか解決方法をご存知の方、ご教授頂けると幸いです！

回答No.5

jamshid6 · ベストアンサー

>FailPackageOnFailure=False にしてしまうと、
シーケンスコンテナ内で、例えば、フラットファイルが存在しない等のエラーが発生してしまっても、成功の制約に進んでしまうのです、、
（つまり、COMMIT TRANに進んでします）

これも普通はありえないですね。無理やり成功にするパラメータはいくつもありますけど、そのどれかが設定されているとしか。
すべてのレベルで、
FailPackageOnFailure=False
FailParentOnFailure=False
MaximumErrorCount=1　でいいはずです。

>こちらは、SQL実行タスク全てに入れておいた方が良いイメージでしょうか？
ROLLBACK TRANだけです。

お礼

ご回答ありがとうございます！

無事、出来ました！
シーケンスコンテナの ForceExecutionResult の値を
変更してしまっておりましたのが原因のようです、、
お騒がせしました。。

しかし、SSISって、設定の関連性が非常に見出しにくいですね、、

jamshid6さまの助けがなければ、どのくらいハマっていたことか、、
改めて、ありがとうございました！！

回答No.4

jamshid6

>ちなみに、失敗時、ROLLBACK TRAN には届かずに終了してしまうのですが、こういうものなのでしょうか？
処理自体はちゃんとROLLBACKされているので問題は無いと思うのですが、、

ROLLBACK TRANに届かないということはないと思いますよ。
それはおそらくどこかの処理でFailPackageOnFailure=Trueになっているということでしょう。
エラーになった場合確かにCOMMITしていないTransactionはROLLBACKされますので、なくても実害はないのですが、
・明示的にROLLBACKした方がフローわかりやすい
・ROLLBACKするが、どこかのテーブルにログは書きたいなどのときは、TransactionModeを終わらせないといけない
などの理由でつけることをお勧めします。

なお、SQL実行タスクでDMLでエラーが生じたときは、そのエラー自体でROLLBACKが発生してしまいますので、
IF (@@TRANCOUNT>0) ROLLBACK TRAN
と書いておくことをお勧めします。

補足

ご回答ありがとうございます。

FailPackageOnFailure=False にしてしまうと、
シーケンスコンテナ内で、例えば、フラットファイルが存在しない等のエラーが発生してしまっても、成功の制約に進んでしまうのです、、
（つまり、COMMIT TRANに進んでします）
これは、ウィザードで作成したものでも同様でした。

> IF (@@TRANCOUNT>0) ROLLBACK TRAN
こちらは、SQL実行タスク全てに入れておいた方が良いイメージでしょうか？

回答No.3

jamshid6

そのイメージで結構です。
SQL Serverはある意味便利なデータベースで、失敗したらDDLだろうがなんだろうがRollBackしてくれます。

余談ですが、データインポート・エクスポートウィザードでSSISパッケージが生成できるのはご存じだと思いますが、これで「多数のテーブル用に最適化する」＋「トランザクションで実行する」というチェックを入れて生成すると、まさにそういうパッケージを作ります。

補足

ありがとうございます！無事、出来ました！
ちなみに、失敗時、ROLLBACK TRAN には届かずに終了してしまうのですが、こういうものなのでしょうか？
処理自体はちゃんとROLLBACKされているので問題は無いと思うのですが、、

>「多数のテーブル用に最適化する」＋「トランザクションで実行する」というチェックを入れて生成すると、
>まさにそういうパッケージを作ります。
これは、気付いていませんでした。。
こちらも、ありがとうございました！

回答No.2

jamshid6

>このケースの場合、やはり、このサイトに記載されているような対応が必要となってくるのでしょうか・・？
http://www.logistech.co.jp/techtips/msdtc.html
そうでしょうね。ただ、私はDTSにせよSSISにせよ、常にローカル（SQL Serverのあるところ）で実行させるようにしているため、その設定は試したことはありません。

なお、こういう場合の別のアプローチを紹介しておきます。
1) 接続マネージャのRetainSameConnection=Trueに設定する
2) Transactionモードで行いたい処理をシーケンスコンテナに入れる
3) 2)の前提として「BEGIN TRANSACTION」だけ行うSQL実行タスクを置く
4) シーケンスコンテナの成功時の後続に「COMMIT TRANSACTION」を行うSQL実行タスクを置き、失敗時の後続に「ROLLBACK TRANSACTION」を行うSQL実行タスクを置く

>最後に、あるサーバ上でそのサーバ上からキックすると、
検証の段階で止まってしまいます。。

これだけでは状況がよくわからないので、なんともコメントできないです。何かエラーメッセージが出るのですか

補足

jamshid6 さま

いつもご回答ありがとうございます。
非常に助かります。

>>このケースの場合、やはり、このサイトに記載されているような対応が必要となってくるのでしょうか・・？
>> http://www.logistech.co.jp/techtips/msdtc.html
>そうでしょうね。ただ、私はDTSにせよSSISにせよ、常にローカル（SQL Serverのあるところ）で実行させるようにしているため、その設定は試したことはありません。

了解いたしました。

>なお、こういう場合の別のアプローチを紹介しておきます。
>1) 接続マネージャのRetainSameConnection=Trueに設定する
>2) Transactionモードで行いたい処理をシーケンスコンテナに入れる
>3) 2)の前提として「BEGIN TRANSACTION」だけ行うSQL実行タスクを置く
>4) シーケンスコンテナの成功時の後続に「COMMIT TRANSACTION」を行うSQL実行タスクを置き、失敗時の後続に「ROLLBACK TRANSACTION」を行うSQL実行タスクを置く

なるほど、、、
イメージとしては、以下でよろしいでしょうか？
(Yes, Noだけでもご回答頂ければ幸いです)

-----------------------------------------------------------
SQL実行タスク(BEGIN TRANSACTION)
※シーケンスコンテナ外
　　　　　　↓
┏━━━━━━━━━━━━━━━━━━━━━━━━┓
┃シーケンスコンテナ(Transactionモードで行いたい処理) ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━┛
　　｜(成功時)　　　　　　　　　　　　　　　　　　　　　　　｜(失敗時)
　　↓　　　　　　　　　　　　　　　　　　　　　　　　　　　　 ↓
SQL実行タスク(COMMIT TRANSACTION)　　　　　SQL実行タスク(ROLLBACK TRANSACTION)
※シーケンスコンテナ外　　　　　　　　　　　　　　　　 ※シーケンスコンテナ外
-----------------------------------------------------------

>>最後に、あるサーバ上でそのサーバ上からキックすると、
>>検証の段階で止まってしまいます。。

>これだけでは状況がよくわからないので、なんともコメントできないです。何かエラーメッセージが出るのですか

そうですよね、、
こちら、新たにログ等の情報が取得出来ましたら、またご教授頂きたく存じます。
（ちなみに、エラーメッセージは何も出ず、処理が凍りついたように全く動かなくなってしまいます。）

回答No.1

jamshid6

該当の接続のRetainSameConnection=Trueにしていますか？
SSISでは、すでに確立済の接続がある場合、Transactionモードを指定できないので、TransactionOptionをRequiredにしたいときは、RetailSameConnection=Falseにしてください。

お礼

--------------------------------------------------
おかげさまでバッチリ実行できました！

ただ、自身のローカル上だけでローカルに対して
実行すると問題なく動作するのですが、
ローカルから別サーバに対して実行しようとすると、以下のエラーが出てしまいます。
--------------------------------------------------
[接続マネージャ "TextMiningDB"] エラー : エラー 0x8004D025 "パートナー トランザクション マネージャにより、
リモート トランザクションまたはネットワーク トランザクションのサポートが無効にされました。" により、
SSIS ランタイムは OLE DB 接続を分散トランザクションに参加させることができませんでした。
--------------------------------------------------
このケースの場合、やはり、このサイトに記載されているような対応が必要となってくるのでしょうか・・？
http://www.logistech.co.jp/techtips/msdtc.html

ちなみに、シーケンスコンテナで括っても、
結局は Required がないとだめなんですね。。

最後に、あるサーバ上でそのサーバ上からキックすると、
検証の段階で止まってしまいます。。
これは、何か考えられる原因はありますでしょうか・・？

補足

たびたびのご回答ありがとうございます。
（神ですか？）

早速試してみます！