none
【バグ?検証用SQL有】SQLServer2005のSSISでSQLServer変換先を指定すると、正常に取り込まれない RRS feed

  • 質問

  • SQLServer2005(SP3)に対し、SSISでOLEDB変換元→SQLServer変換先を利用して、データのエクスポートを行っています。
    今までは問題なく動作しておりましたが、フィールドを追加してから、データが正常に取り込まれなくなり、 
    varchar(max)に以降のデータが入り込むという現象が起きてしまいます。

    実際現象が起きるデータとは異なりますが、以下の手順で同様の現象が確認できるかと思います。
    当方3か所の別環境で検証しましたが、すべて同様の現象が発生しました。

    ① 転送元、転送先となるインスタンス( 既定の設定 )を任意の名称で作成し、それぞれに以下のテーブルを作成。
    ※インスタンスは同サーバ内に作成しても問題ありません。(別サーバのインスタンスでも同様の現象が発生します)

    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[T_TEST](
    [Column0] [int] NOT NULL,
    [Column1] [char](1) NULL,
    [Column2] [decimal](11, 0) NULL,
    [Column3] [nvarchar](max) NULL,
    [Column4] [char](1) NULL,
    [Column5] [decimal](11, 0) NULL,
    [Column6] [nvarchar](max) NULL,
    CONSTRAINT [PK_T_TEST] PRIMARY KEY CLUSTERED
    (
    [Column0] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF 

     


    ② エクスポート用のテーブルに以下のSQLを流し、データを作成します。16384件データが作成されます。

    ----データ作成
    DECLARE @CNT INT SET @CNT = 1
    DECLARE @SQL NVARCHAR(1000)
    DECLARE @SETSTRING NVARCHAR(23) SET @SETSTRING = REPLICATE('あ',23)
    
    --1レコード作成
    SET @SQL = N'INSERT INTO T_TEST (COLUMN0, COLUMN3, COLUMN6) ' +
     'VALUES (' + CONVERT(CHAR,@CNT) + ', ''' + @SETSTRING + ''',''' + @SETSTRING + ''')'
    PRINT @SQL
    EXECUTE SP_EXECUTESQL @SQL
    --繰り返し作成
    WHILE @CNT <=14
    BEGIN
     insert into t_test ( column0, column3, column6)
     select column0 + (select max(column0) from t_test), column3, column6
      from t_test
    
     SET @CNT = @CNT + 1
    END 

    ③ SSISにて、Integration Services プロジェクトを作成し、制御フローに「T-SQL ステートメントの実行タスク」「データフロータスク」
    を追加。

    ④ T-SQLステートメントの実行タスクに、以下を記載。
    truncate table [転送先インスタンス名].[dbo].[T_TEST]
    
     
    ⑤ データフロータスクを、
    転送元をOLEDBソース、転送先をSQLServer変換先とし、全フィールドがエクスポート対象と
    なるよう設定。
    ※設定はすべて既定

    ⑥ ⑤を実行

    ■結果
    ・16384件中9198件までしかインポートされていない
    ・9198件目のColumn6(varchar(max)の列)の文字数が819226文字

    ■追記
    ・9196,9197件目のColumn6を0文字にして実行すると、全件正常に取り込まれる
    ・varchar(50)に変更すると、全件正常に取り込まれる

    ■現行の退避策
    ・SQLServer変換先をOLEDB変換先に変更(正常に取り込まれたことを確認)
    →日次でお客様に件数確認のメールを送信

    本番環境でお客様が利用しているサーバで同現象が発生しており、検証結果をお送りし説明しましたが
    公式サイトに何も記載がないため「なぜ起きるかわかりません」というわけにもいかない状態です。
    エラー発生時にはエラーメールを送信する設定になっておりますが、同現象は「正常完了」メールが届きます。
    (デバック画面でも正常となります)。

    SQLServerの公式サイトの説明で、SQLServer利用時にSQLServer変換先を利用することを推奨しておりますが、
    上記現象が出たためこのまま利用するには信頼性にかけるので、正常終了メールが来ても毎日件数チェックを行っております。
    (検証は1テーブルですが、本番は多数テーブルがありますが、「他は件数に問題がなかったから変更しないでほしい」という
    お客様の要望により、現象が発生したテーブルのみ変更している状態です)

    また、現状OLEDB変換先を利用しておりますが、対応としてこれで問題ないのかというのも確証が持てないため、
    どのような対応をすればいいのか、わからない状態です。
    多数検証を行いましたが、原因がどこなのかというのが特定できません。
    (varchar(max)でも他フィールドの組み合わせによっては発生しない)


    通常のSQL文での対応も検討しておりますが、検証したところ処理時間が倍になるのでできればSSISを
    利用していきたいのですが…。

    どういう原因で上記のような現象が発生するのかというのも、特定したいのですが
    他に、サーバの設定を変更する必要がなく、高速で別サーバにデータをインポートする方法がありましたら
    ご教授頂ければと思います。

    長くなりましたが、よろしくお願い致します。
    2010年8月9日 5:45

回答

  • ほんとにわからないのですが、

    SSIS以外で実現する場合、どうするか、という観点からいくと、

    ケースバイケースですが、適当にコミットいれるかな、というところです。

    件数はともかく、[nvarchar](max)も気になりました。

    2008だと起こらないようですし、深く追求してもどうかと。。。

    先のプロパティ変更は、エラーの時にどうなるかも確認したほうがよいですね。

     

    他、試してないのですが、リンクサーバを作って、select - insert は

    できないのでしょうか。

     

     

    • 回答としてマーク shiro_kom 2010年8月25日 1:08
    2010年8月24日 14:00

すべての返信

  • 列、行どちらかのターミネータを認識できずにスキップしてしまうようですね。

    > 9196,9197件目のColumn6を0文字にして実行すると、全件正常に取り込まれる

    9196,9197件目のColumn6って、どんな文字列が入っていますか?'ああああ....ああああ' ですか?

    ほかには、システム ロケールを、たとえば英語にしてから同じことをやってみても同じ問題がおきますか?

    システム ロケールを変更する方法は OS によって微妙に違うので、windows システム ロケール 変更 で検索してみてください。

     


    回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう
    2010年8月18日 6:18
  • 手元にあった SQL Server 2008 R2 x64 ENU で試してみたのですが、すくなくともこの環境では問題が起きませんでした。どこかにバグがあるのだろうと思いますが、すぐに考え付くのはこのあたりです。

    1. システム ロケール依存の問題 (system locale = Japaneseの時だけ起きる問題)
    2. SNAC 9.0 -> SNAC 10.5 の間に修正された問題にあたった
    3. SQL Server or SSIS 2005 -> 2008 R2 の間に修正された問題にあたった

    Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう
    2010年8月19日 3:52
  • M_Lewis様、ご回答いただきましてありがとうございます。

    > 9196,9197件目のColumn6って、どんな文字列が入っていますか?'ああああ....ああああ' ですか?

    全てのデータにおいて、'あ'が23文字です。

    ご回答いただいた内容を参考に、ロケールの変更を行い、
    データで挿入する文字列を'a'(半角)の23文字に修正し、データを作成しましたが
    同様の現象が発生しました。

    また、2008で動作検証を行って頂きましてありがとうございました。

    原因の特定が難しいことは重々承知の上ですが、
    データ転送のSSIS以外の高速な方法についての検討も含め、
    まだ、いろいろな方のご意見も伺いたいので
    回答済みについては保留とさせて頂きます。

    簡単な変更しか行っておりませんが、今回利用したデータ作成のコードを貼っておきます。

    ----データ作成
    DECLARE @CNT INT SET @CNT = 1
    DECLARE @SQL NVARCHAR(1000)
    DECLARE @SETSTRING NVARCHAR(23) SET @SETSTRING = REPLICATE('a',23)
    
    --1レコード作成
    SET @SQL = N'INSERT INTO T_TEST (COLUMN0, COLUMN3, COLUMN6) ' +
     'VALUES (' + CONVERT(CHAR,@CNT) + ', ''' + @SETSTRING + ''',''' + @SETSTRING + ''')'
    PRINT @SQL
    EXECUTE SP_EXECUTESQL @SQL
    --繰り返し作成
    WHILE @CNT <=14
    BEGIN
     insert into t_test ( column0, column3, column6)
     select column0 + (select max(column0) from t_test), column3, column6
     from t_test
    
     SET @CNT = @CNT + 1
    END 
    

     

    • 編集済み shiro_kom 2010年8月24日 6:52
    2010年8月20日 4:32
  • SQLサーバ2005(SPなし)で発生しました。同一インスタンス内ですが。

    SSISで、緑色(成功)で、元テーブル件数分表示されるので困りますね。。。


    プロパティを色々さわってみましたが、

    「SQLサーバ変換先」の

    MAXINSERTコミットサイズ を適当に1000にしたら

    全件INSERT成功しました。(同時に幾つか触ってましたが、これだと思います)

     

    ぼんやりと想像はつくのですが、説明できるレベルではありません。

     

    2010年8月20日 18:43
  • pomodoro様、ご回答いただきましてありがとうございます。
    SSISで、緑色(成功)で、元テーブル件数分表示されるので困りますね。。。
    そうなんです…エラーとして認識されないので、結果を確認しないことにはわからない…というのが、
    とても困ってしまう所です…。
    プロパティを色々さわってみましたが、
    「SQLサーバ変換先」の
    MAXINSERTコミットサイズ を適当に1000にしたら
    全件INSERT成功しました。(同時に幾つか触ってましたが、これだと思います)
    「SQLServer変換先」のMaxInsertCommitSizeプロパティを1000にしたら、当方の環境でも全件Insertに成功しました。
    他のプロパティを変更せずに確認致しました。
    該当の現象が発生する数値よりも少なく設定することで、正常に取り込まれることを確認しました。

    10000にすると、やはり正常に取り込まれませんが、こちらについては数値を見極めて
    全件数を考慮した数値を設定すれば問題ないのかと思いますので、
    この部分が確定すれば、SQLServer変換先を利用できそうです。
    ぼんやりと想像はつくのですが、説明できるレベルではありません。
    ぼんやりでも全然構いません。
    当方、憶測もつかない状態なので、
    図々しいお願いかとは思いますが、pomodoro様のご意見頂ければ幸いです。
    2010年8月24日 5:24
  • ほんとにわからないのですが、

    SSIS以外で実現する場合、どうするか、という観点からいくと、

    ケースバイケースですが、適当にコミットいれるかな、というところです。

    件数はともかく、[nvarchar](max)も気になりました。

    2008だと起こらないようですし、深く追求してもどうかと。。。

    先のプロパティ変更は、エラーの時にどうなるかも確認したほうがよいですね。

     

    他、試してないのですが、リンクサーバを作って、select - insert は

    できないのでしょうか。

     

     

    • 回答としてマーク shiro_kom 2010年8月25日 1:08
    2010年8月24日 14:00
  • pomodoro様、たびたびの回答ありがとうございます。
    件数はともかく、[nvarchar](max )も気になりました。
    こちらについては、nvarchar(max)が影響しているのは、多分あたっていると思います。
    maxを固定値に変更した場合は、発生しないことを確認済みです。

    nvarchar(max)が存在する組み合わせのみで検証を行い、
    nvarchar(max),dechimal(11,0),char(1)の組み合わせで、nvarchar(max)の行が特定の列に達すると
    発生する、ということを確認し、テストデータを作成しました。
    組み合わせが違えば、フィールドが大量にあってもnvarchar(max)のフィールドも全件取り込めるので、
    何かしら組み合わせ条件があるのだと思います...。
    2008だと起こらないようですし、深く追求してもどうかと。。。
    先のプロパティ変更は、エラーの時にどうなるかも確認したほうがよいですね。
    そうですね。おっしゃる通り2005のSSISでは当方の環境以外でも発生することが
    確認できたので、コミットorSQLで検討していきたいと思います。
    プロパティ変更については、エラー時もやはり正常扱いとなってしまい、件数も全件
    取り込まれたようになってしまいますが、分岐などでも対処できそうなので、
    検討事項に取り入れたいと思います。
    他、試してないのですが、リンクサーバを作って、select - insert は できないのでしょうか。
    最初の投稿で「SQL文で~」と記載したのは、Select - Insertを想定していて、時間が倍程度かかってしまうのですが
    他に方法がなければこちらで対処しようと思います。
    適当なところで、コミットを入れる件も検証してみます。

    貴重なご意見を頂きまして、非常に参考になりました。
    ありがとうございました。
    2010年8月25日 1:08