none
SSIS DB2 OLE DB source hangs for more rows

    Question

  • HI,

    I have a package which has DB2 DB as the source and SQL Server 2008 R2 as the destination. Its a simple and straightforward query to pull data from a table. I tested it on the DB2 DB and it works fine.

    On the SSIS OLE DB source, if i limit the data pull to FETCH FIRST 800 ROWS ONLY, on the DB2 end, it works. But if i increase the limit of rows fetched to 900, it hangs forever.

    I have tried different combinations eg: select one columns; select 2 columns, etc

    It works when I fetch a random number of rows and doesn't when i fetch another random number of rows.

    The DB2 DBA confirmed that there was no deadlock on the DB2 end, when this package is running too.

    Any help/suggestion please?

    Regards,

    NL

     

    Tuesday, July 16, 2013 9:56 PM

Answers

  • OK, Could you please use ADO.NET source and use .NET providers for IBM to connect to DB2? Please make sure you have updated drivers installed for this process....

    Thanks, hsbal

    • Marked as answer by LionKingNL Tuesday, July 23, 2013 6:36 PM
    Tuesday, July 23, 2013 12:55 PM

All replies

  • In SSIS data flow source , Please set the

    AccessMode to SQLCommand

    CommandTimeOut to 0

    In destination please put 

    Rows Per Batch = 800

    Maximum Insert Commit Size = Default value

    and try again please and let me know what happens


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 3:04 PM
  • Hi NL,

    This link help you to understand, how may rows SSIS passes down it's pipeline and performs data loading...

    http://microsoft-ssis.blogspot.com/2013/04/performance-best-practice-more-rows-per.html


    Thanks, hsbal

    Monday, July 22, 2013 7:50 PM
  • In SSIS data flow source , Please set the

    AccessMode to SQLCommand

    CommandTimeOut to 0

    In destination please put 

    Rows Per Batch = 800

    Maximum Insert Commit Size = Default value

    and try again please and let me know what happens


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Those are default options right? nothing to change in there?

    Please Help People When they need..!!

    Monday, July 22, 2013 8:16 PM
  • Hi Prajesh,

    Thanks a lot for your reply. But since these are default options, I didn't see any difference.

    I double checked the values and they were already set to what you have specified (except for Rows Per Batch).

    But still no luck :(

    Any other suggestions please...

    Regards,

    NL


    Tuesday, July 23, 2013 3:20 AM
  • Hi hsbal,

    Thanks a lot for your reply. I checked out the link. I am using a query with limited columns in the select query. I am also using a where clause and the RunInOptimizedMode option is set to True. But the package hangs at the OLE DB task :(

    Any other suggestion please...

    Regard,

    NL

    Tuesday, July 23, 2013 3:26 AM
  • OK, Could you please use ADO.NET source and use .NET providers for IBM to connect to DB2? Please make sure you have updated drivers installed for this process....

    Thanks, hsbal

    • Marked as answer by LionKingNL Tuesday, July 23, 2013 6:36 PM
    Tuesday, July 23, 2013 12:55 PM
  • Hi hsbal,

    It works!!! :) Thanks a lot for your help.

    Regards,

    NL

    Tuesday, July 23, 2013 6:18 PM