Using "Loop Connections" option in Automation

I am using “Loops Connections” option in my automation workflow where in i define my connections.

Under “loop connections”, I have define “loop dataset” where in i have defined a query. (SELECT * from ADMIN.TEMPLATE).

Under Activity input table of Loop data set i have defined the connection as “root@localhost” , as I want it to use the connection specified under “Loops Connections

When i execute a workflow i am getting following error.

7/30/2018 10:21:31 AM Connecting to root@localhost
7/30/2018 10:21:31 AM Table ‘admin.template’ doesn’t exist

Table Template exist in DB. Workflow executes fine if i explicitly mention the Database name in the in Loop data set activity. But i want the loop dataset activity to take the database name from Loop connections

Let me know if anyone needs more info to resolve this.

The connections you specify in the Loop Connections activity take precedence over any connection specified in an individual activity. But “loop dataset” does not for TDP4.2&4.3 &5.0. it is a bug, and I have created issue to follow it.

We never coded for this nested support. How many connections do you have in your Loops connections? Hopefully it is a short list so that setting each Loop dataset manually would be a workable solution.

Hi Debbie and Qing,
I have the exact same need as NikhilP. We have automated the messaging for end-user DB password expirations and now want to expand it to other Oracle databases. The Loop Dataset does not take on the connection from the Loop Connection as previously stated. I see that QAT-13279 has not been addressed in the current beta. Any chance that this is going to make it into the 5.1 dot release?

We had some staff changes. i need to assign automation to a new developer and then schedule the work. We are also planning to move to monthly releases. So TDP 5.0 gets released in Feb. Perhaps we can get it for 5.0.1 or 5.0.2. No promises but it should not be a hard one to fix.

I have a question for both users who want to use Loop connections with nested Loop Dataset. We have coded to use the current connection in the first step of the loopdataset. But what about any children added to the Loop dataset? These can be set to a different connection. Do you need the child activities of the loopdataset to use the loop connections also? Or you want these to be different connections.

Hi Debbie,
My need is running the same query on multiple servers, so the child processes would be different database connectors. For instance cycling through DEV/QA/TEST/PROD to see if you get equivalent results sets.
Thanks for opening a ticket on this. I hope that there’s room to squeeze it into the 5.1 release.

I still need to clarify. The child activity I am referring to would be the child activity of the loop dataset. Loop datasets have a connection for the driving query of the loop and then each child activity can be different connections. See this video.
So we have it coded that if a loop dataset is used with loop connection the first query of loop dataset will use the current loop connection. But we do not have any of the child activities of the loop dataset using that connection.
So in your example the loop connections would use a list of connections for Dev/QA/Test/Prod and the first query of the loop dataset would use what ever the current connection is. What do you have has a child activity in the loop dataset and does that need to be the same connection as current loop connection. Or will that connection be to something you set in that activity and never change?

Hi Debbie,
I'm expecting the Loop Connection to behave in the same manner as the Loop Dataset functionality that I routinely use to burst emails out to multiple recipients. Would it be possible to specify the active connection for a child EXECUTE process to be passed as a variable? It appears that Loop_connections_1_CONN is being set in the outer loop as 1 of 4 possible values in my screen capture <see below>. TDP 4.3 does run 4 times, but the connection never changes and I don't see any status icons change in the Navigation Manager window. In my case it runs against PHPEDW01 every time instead of substituting the connections added to the outer loop list.

Why do you have a loop connections followed by Database Connection and then an execute activity? If you take out the activity named “Connection_1” and have the execute_1 as the child activity it does use the current connection from the loop connection. You can see it in the log file. In my test I used

We decided to add an option in the activities to use parent connection or not. this will solve all scenarios. It will be in the TDP 5.0.3 release in mid April