locked
SSIS connection error RRS feed

  • Question

  • I am calling a stored procedure (sql 2008) through SSIS (2008) inside a for container. The call works for about 5 to 15 minutes before failing with IDbConnection.Open failed. I don't seem to get any error in the stored procedure. (using an ADO.NET connection)

    Other information:
    1. Re-running the SSIS package does not throw the error and continues working for the next 5-15 minutes before failing with the same error
    2. I do not think that the error is data specific
    3. I am invoking a CLR stored procedure from within the T-SQL procedure. (This definitely succeeds)

    Any help is much appreciated.

    Thanks,

     
    Monday, February 9, 2009 1:48 AM

Answers

  •  try this:

    for the connection manager, set RetainSameConnnection = True

    hth

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Monday, February 9, 2009 4:34 AM
    • Marked as answer by Bob Bojanic Thursday, February 12, 2009 6:15 PM
    Monday, February 9, 2009 4:34 AM

All replies

  • More info: I now got an error which said that the time out period elapsed before it could get a connection. All connections in the pool were used. I found that lots of connections were being used though, there is no reason why. Is there a configuration that I am missing? Does for container open up many connections while it loops?

    Monday, February 9, 2009 2:04 AM
  •  try this:

    for the connection manager, set RetainSameConnnection = True

    hth

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Monday, February 9, 2009 4:34 AM
    • Marked as answer by Bob Bojanic Thursday, February 12, 2009 6:15 PM
    Monday, February 9, 2009 4:34 AM
  •  Setting RetainSameConnection to true did not seem to fix the problem. However, I could not repro this problem on a WinServer2003 machine. I also did not check this in other WinServer 2008 machines too.

    Moreover, for my situation, I moved the loop inside the Stored procedure I am calling, and thereby improved my performance way too much. With this of course there are no connection explosions at all. Thanks Duane Douglas for your reply.
    Friday, February 13, 2009 7:47 AM