Post reply

Stored Procedure in Foreach Loop

  • October 30, 2008 at 3:47 am

    #131810

    Hi!

    I've got a table and I wish to to call a stored procedure for each row in the table.

    1. Is the Foreach Loop Container the way to go?

    2. How do I get hold of the table to do the loop?

    3. The stored procedure is executed in an Execute SQL Task and takes the id from each row as in-parameter, how do I get hold this value? I guess it's done with Variable and Parameter mapping, but I can't get the hang of it.

    Any help is much appreciated!

    /Stefan

  • October 30, 2008 at 7:41 am

    #891771

    Stefan (10/30/2008)

    1. Is the Foreach Loop Container the way to go?

    Yes, you would use a Foreach loop. You would use the Foreach ADO Enumerator.

    2. How do I get hold of the table to do the loop?

    You would use an Execute SQL Task with the ResultSet set to Full result set and then on the Result Set tab you would map the result set (0) to a an Object variable which you will use in the Foreach loop as the Source.

    3. The stored procedure is executed in an Execute SQL Task and takes the id from each row as in-parameter, how do I get hold this value? I guess it's done with Variable and Parameter mapping, but I can't get the hang of it.

    In the Foreach loop Variable Mappings tab you would take the ID column ordinal (0, let's say) and map it to your id variable.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 21, 2009 at 11:39 am

    #1056403

    Hi Jack Corbett-

    I have the same requirements to be done in SSIS.

    That is, I need to loop thru all the records in a staging table and call a stored procedure for each record. I need to send 5 parameters from the current record to the stored procedure as input. And get the output of the stored procedure and based on that I need to update back the staging table.

    I am new to SSIS. Can I get the step by step instructions on how to do it?

    Your help is much appreciated.

    Thanks.

  • September 21, 2009 at 1:27 pm

    #1056458

    Yes, you can iterate through a result set and call a stored procedure, but I'd be real carefull about what you use this for. You're creating a RBAR process!

    What could you possibly be doing where you'd need to call a SP for every row in the table?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • September 21, 2009 at 1:39 pm

    #1056465

    My staging table will have all the records that needs to be imported in to a ERP system with the status flag set to 0. And this Staging table is feeded by another system.

    So I need to loop thru all the records with the status flag 0 and call a stored procedure. This stored procedure have the business logic for creating a record in a ERP system and it is locked. So I can only send the parameters to it.

    If I get the output parameter from the Stored procedure as "success" then I need to update the Staging table status flag as 1.

    Thanks,

  • September 21, 2009 at 1:55 pm

    #1056470

    This should be done inside a data flow, not using a stored procedure and for each loop.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • September 21, 2009 at 2:12 pm

    #1056477

    So what controls in the Dataflow I should use to get this done?

    Thanks

  • September 21, 2009 at 2:22 pm

    #1056478

    That would depend on what you would have done inside your stored procedure. Can you tell me what your SP logic would have done?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • September 22, 2009 at 1:05 pm

    #1057070

    Hi there-

    The stored procedure is locked and it is from the corresponding ERP system.

    I found an alternate way to do this by using OLEDB Command. But I have one other issue on using the OLEDB Command.

    My stored procedure have 5 input parameters and two output parameters. And I want to send only the first, third and fifth parameter as input.

    I tried using the syntax given below

    EXEC CreateCustomer @I_vCUSTNMBR = ? , @I_vCUSTNAME = ? , @I_vCUSTDESC = ?

    @O_iErrorState = ? OUTPUT ,

    @oErrString = ? OUTPUT

    But it takes all of them as input parameters.

    Any idea on this?

    Thanks,

  • September 22, 2009 at 1:38 pm

    #1057095

    If the SP logic is not something that you can dulicate inside a data flow, I would suggest backup up and using the For Each Loop container with an Execute SQL task to run your SP. This will allow you to take the columns that you want from your staging table and iterate through them using an ADO recordset and the For Each Loop container.

    If you are forced to creating a loop, I would not use the OLE DB Command.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

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