Retrieve data from same table in different databases at different times of the day

Question

Hi All,

I have to retrieve data from the same table in different databases at different times of the day. To achieve that I have created a control table to choose the schema and connection strings for different databases. I wanted to schedule the workflow on the server so that it can run at different times of the day and hit the different databases based on the schedule and connection strings at different times.

The logic that I was implementing was :

1) I have a schedule column in the control table and I was comparing the current date time in the scheduler to the scheduled time so that I can filter the row for the right connection string. The problem with that is if the workflow goes in the queue the check for time comparison fails and data cant be retrieved.

2) Is there a better way to achieve this?

Control table.PNG

DanielG · Answer

Yes.  I agree 135 workflows is a bit too much to maintain like that.  🙂

Could you create some sort of simple separate output in the workflow that contains the details of the items used in that run of the workflow, and then when the next run goes it uses that simple file an input to find the next one to run on the list?  When it gets to the bottom, it obviously would need to be smart enough to move back to the top of the list.

Almost like an iteration in an iterative macro, but obviously not run consecutively in the same workflow.

Or is there a way to build this as an iterative macro, even?

Just thinking and typing at the same time, so how to do it I am not sure, but I think the concept might work...

🙂

sjain71 · Answer

The problem with that we have 35 tables and 5 different target table structure. In total, we will have 35*5 = 175 workflows, which will have performance issues. I want 35 workflows, one workflow for each table and then dynamically change the connection strings (using a macro) which I have already built. But it's just filtering the rows with the right connection strings is the issue.

DanielG · Answer

While this isnt slick solution, I personally would create a separate workflow for each of the end databases you want to connect to and schedule those individually according to when you need each database hit.

To me it seems likes a lot of complexity to build out, when the simple approach from above will easily account for those "queued" issues by just having as standalone workflows.

Good luck 🙂