Post reply

Execute SQL Task fails for large volume of data

  • April 1, 2016 at 7:40 am

    #146032

    I have an Execute SQL Task that executes a stored procedure and is expected to return one output parameter, and a Result Set that is stored in an SSIS object variable.

    The task works fine with small volume of data. But The current requirement needs to fetch about 1.1 million records. The task fails for such large volume, giving the error - 'Object was open'.

    I could have used a Data Flow Task directly, but there are 2 concerns : 1) The SQL command to execute is dynamic and 2) I need to fetch the value of output parameter returned by the stored procedure.

    Any help would be appreciated.

    Thanks in advance.

  • April 1, 2016 at 9:30 am

    #1869208

    tejalchaudhari89 (4/1/2016)

    I could have used a Data Flow Task directly, but there are 2 concerns : 1) The SQL command to execute is dynamic and 2) I need to fetch the value of output parameter returned

    1. You can use dynamic code in a data source in a DFT by using a variable. http://www.sqlsafety.com/?p=266

    2. Results from the T-SQL OUTPUT clause work in SSIS. http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/63623/

    I'm on LinkedIn

  • April 4, 2016 at 4:04 am

    #1869437

    Thanks for your help.

    #1 is something I would like to use. But I have one additional requirement to the solution proposed in it. I need to get value of OUTPUT parameter returned by store procedure and save it in an SSIS package variable. I am stuck at how to fetch this OUTPUT parameter

    #2, this is what my package currently implements. But this solution is failing for larger number of records. I need to find an alternate solution

  • April 4, 2016 at 4:46 am

    #1869441

    tejalchaudhari89 (4/4/2016)

    Thanks for your help.

    #1 is something I would like to use. But I have one additional requirement to the solution proposed in it. I need to get value of OUTPUT parameter returned by store procedure and save it in an SSIS package variable. I am stuck at how to fetch this OUTPUT parameter

    #2, this is what my package currently implements. But this solution is failing for larger number of records. I need to find an alternate solution

    Okay, if your output is too big for the SSIS buffer then it will cause an out of memory error. Can you not store the output in a temporary table and reference that in your Control/Data flow?

    I'm on LinkedIn

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply