none
Timeout expired RRS feed

  • Question

  • Hi,

     Our Biztalk server 2009 is on different machine and Database (SQL Server 2008) is in different machine.
    we have implemneted bulk insert adapter in biztalk while source loading.the  source file format is .csv.i have checked the databse connection pool size also and it is more then 32000.the source location is at machine where database resides.
    i am getting error" The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached".

    can any one suggest what wrong i am doing.

    Regards,
    saurabh


     

    Tuesday, December 15, 2009 10:17 AM

All replies

  • All the documentation I can find shows BizTalk 2004 or 2006 for use with the SqlBulkInsert adapter. Do you know if it officially works with BizTalk 2009 and SQL 2008?

    I would wonder if the APIs have changed and the adapter no longer works with them successfully.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, December 17, 2009 4:25 AM
    Moderator
  • Could you please send us the entry in the event log that you might be getting?
    Thursday, December 17, 2009 10:01 AM
  • Hi ,

    thanks for the reply.actually what i did just downloaded the bulkinsert adapter code and double clicked on registry.then added bulk insert adpter.
    could you please suggest me where i should change the API in bulkinsert dapter code?since we are using BizTalk 2009.

    My thought on above issue is
    if i increase the pool size while configuration of dapter may resolve the problem. since default is 100.so i made it to 150.

    SQL Connection String-----
    packet size=4096;integrated security=SSPI;data source="(local)";persist security info=False;initial catalog=CDMM;Max Pool Size=150

    since this is production issue so i can't experiment.i m not facing any problem on staging.

    what is your suggestion about my approach?whethere i should implement it on production?


    Regards,
    Saurabh
    Monday, December 21, 2009 5:15 AM
  • Hi ,

    please find the error detail


    Event Type: Warning
    Event Source: BizTalk Server 2009
    Event Category: (1)
    Event ID: 5740
    Date:  12/17/2009
    Time:  3:43:34 AM
    User:  N/A

    Description:
    The adapter "SqlBulkInsert" raised an error message. Details "Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.".

    Regards,

    saurabh

    Monday, December 21, 2009 5:19 AM
  • Try extending the connection timeout using something like this on the connection string:  "Connect Timeout=45".

    This link gave this suggestion: http://www.15seconds.com/Issue/040830.htm

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, December 22, 2009 2:15 PM
    Moderator