Execute final stored procedure after multiple tables import.

  • 20 April 2023
  • 3 replies

Userlevel 1

I have a flow that processes multiple files into multiple Sql Server tables. Once all those database tables are updated with the imported data, I want to run a stored procedure that uses that data to populate other tables. I was thinking I would just add another Sql Server connector after the import_pricing connector, but can I be sure that all the other import connectors have successfully completed first?  Is there a proper way to do this in Arc, so that when I run the final stored procedure all the needed import tables are populated appropriately first?




Best answer by James B 20 April 2023, 23:35

View original

3 replies

Userlevel 4

The difficulty in doing this is that each of these flows would operate asynchronously, so there isn’t a good way to tell from one SQL Server connector that all of the SQL Server connectors have finished processing, or even if they’re working on the same data set (for example. since the operation is happening asynchronously, you’d need to know that the last update to the locations table was part of the same data set that updated the items table.

With that said, it is easy enough to call a stored procedure after all of the data in a set has been uploaded by including an embedded call to the stored procedure within the input mapping:

This ensures that a stored procedure call would be triggered after all of the items for one table are uploaded successfully. 

My initial thought on how to approach this is to use four distinct stored procedure calls that work in parallel. Each one would update its own row in a simple staging table as ready for processing: 


Table_Type Ready_To_Go
locations false
items false
customers true
pricing true


And then if and only if all of the rows were true, begin processing the set, and then set all of the values to false. In this way, each path in the flow will call the stored procedure, but it’s always the last one that kicks off the process.


Is that something that you have control over? 


Userlevel 1

Hi James,

Thanks for the reply and the advice.  I’ll give your suggestion a try.



Userlevel 1

Just an update:

I used the pattern James suggested with some more details added.

  1. Based on the Database Advanced Configuration help, I added a `<runFirst>` and `<runLast>` stored procedure call to prepare can then calculate the data.
  2. In the `<runLast>` sproc, I log the table that just imported and then check to see if all needed tables are imported.  If they are then I run the logic to update the appropriate table data.

So far, everything works flawlessly.