- Posts: 9
- Thank you received: 5
Nested Database Loop executes once and stops
7 months 2 weeks ago - 7 months 2 weeks ago #25856
by Onkar2002
Nested Database Loop executes once and stops was created by Onkar2002
I have a package with a Nested Database Loop structure:
Outer Loop: Selects Employees with pending tasks.
Inner Loop: Selects tasks for that specific employee (WHERE Code = '#Code#').
Action: Sends an email.The Issue: The package processes the first employee successfully (sends the email) but then terminates immediately. It does not loop to the second record, even though valid data exists.
Database: SQL Server
Troubleshooting: I confirmed the Inner Loop handles NULLs and does not error out.
Here is my Outer Loop Query (Gets the list of people):
SELECT DISTINCT
[HR1 Merged].Code,
[HR1 Merged].EName,
[HR1 Merged].Email
FROM
basecamp.[HR1 Merged]
INNER JOIN basecamp.checklist_master
ON [HR1 Merged].Code = checklist_master.hr1_code
INNER JOIN basecamp.checklist_tasks
ON checklist_master.MasterTaskID = checklist_tasks.MasterTaskID
WHERE
checklist_master.is_proof = 1 AND
checklist_master.is_deleted = 0 AND
checklist_tasks.DueDate < CAST(GETDATE() AS DATE) AND
checklist_master.AutoDropOverdue = 0 AND
checklist_tasks.Status = 'Not Started'
Here is my Inner Loop Query (Generates the HTML Rows):
SELECT
CAST((
SELECT
'td' = CAST(checklist_tasks.GeneratedTaskID AS VARCHAR(20)), '',
'td' = checklist_master.TaskDescription, '',
'td' = CAST(checklist_tasks.DueDate AS VARCHAR(20)), '',
'td' = CAST('<a href=" proof.krishnabeads.com/?task_id= ' +
CAST(checklist_tasks.GeneratedTaskID AS NVARCHAR(20)) +
'" style="background-color: #4CAF50; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;">Submit</a>' AS XML)
FROM
basecamp.checklist_master
INNER JOIN basecamp.checklist_tasks
ON checklist_master.MasterTaskID = checklist_tasks.MasterTaskID
WHERE
checklist_master.is_proof = 1 AND
checklist_tasks.DueDate < CAST(GETDATE() AS DATE) AND
checklist_master.AutoDropOverdue = 0 AND
checklist_tasks.Status = 'Not Started' AND
checklist_master.is_deleted = 0 AND
checklist_master.hr1_code = '#Code#'
FOR XML PATH('tr'), ELEMENTS
) AS NVARCHAR(MAX)) AS TasksHTMLTable
Here is the Email HTML Body:
<body>
<p>Dear #EName#,</p>
<p>The following are your Pending Proof Tasks. Please complete them ASAP.</p>
<table border="1" cellpadding="8" cellspacing="0" style="border-collapse: collapse; width: 100%; border: 1px solid #ddd;">
<thead>
<tr style="background-color: #f2f2f2; text-align: left;">
<th>ID</th>
<th>Description</th>
<th>Due Date</th>
<th>Action</th>
</tr>
</thead>
<tbody>
#TasksHTMLTable#
</tbody>
</table>
<p>Thank you.</p>
</body>
Outer Loop: Selects Employees with pending tasks.
Inner Loop: Selects tasks for that specific employee (WHERE Code = '#Code#').
Action: Sends an email.The Issue: The package processes the first employee successfully (sends the email) but then terminates immediately. It does not loop to the second record, even though valid data exists.
Database: SQL Server
Troubleshooting: I confirmed the Inner Loop handles NULLs and does not error out.
Here is my Outer Loop Query (Gets the list of people):
SELECT DISTINCT
[HR1 Merged].Code,
[HR1 Merged].EName,
[HR1 Merged].Email
FROM
basecamp.[HR1 Merged]
INNER JOIN basecamp.checklist_master
ON [HR1 Merged].Code = checklist_master.hr1_code
INNER JOIN basecamp.checklist_tasks
ON checklist_master.MasterTaskID = checklist_tasks.MasterTaskID
WHERE
checklist_master.is_proof = 1 AND
checklist_master.is_deleted = 0 AND
checklist_tasks.DueDate < CAST(GETDATE() AS DATE) AND
checklist_master.AutoDropOverdue = 0 AND
checklist_tasks.Status = 'Not Started'
Here is my Inner Loop Query (Generates the HTML Rows):
SELECT
CAST((
SELECT
'td' = CAST(checklist_tasks.GeneratedTaskID AS VARCHAR(20)), '',
'td' = checklist_master.TaskDescription, '',
'td' = CAST(checklist_tasks.DueDate AS VARCHAR(20)), '',
'td' = CAST('<a href=" proof.krishnabeads.com/?task_id= ' +
CAST(checklist_tasks.GeneratedTaskID AS NVARCHAR(20)) +
'" style="background-color: #4CAF50; color: white; padding: 5px 10px; text-decoration: none; border-radius: 3px;">Submit</a>' AS XML)
FROM
basecamp.checklist_master
INNER JOIN basecamp.checklist_tasks
ON checklist_master.MasterTaskID = checklist_tasks.MasterTaskID
WHERE
checklist_master.is_proof = 1 AND
checklist_tasks.DueDate < CAST(GETDATE() AS DATE) AND
checklist_master.AutoDropOverdue = 0 AND
checklist_tasks.Status = 'Not Started' AND
checklist_master.is_deleted = 0 AND
checklist_master.hr1_code = '#Code#'
FOR XML PATH('tr'), ELEMENTS
) AS NVARCHAR(MAX)) AS TasksHTMLTable
Here is the Email HTML Body:
<body>
<p>Dear #EName#,</p>
<p>The following are your Pending Proof Tasks. Please complete them ASAP.</p>
<table border="1" cellpadding="8" cellspacing="0" style="border-collapse: collapse; width: 100%; border: 1px solid #ddd;">
<thead>
<tr style="background-color: #f2f2f2; text-align: left;">
<th>ID</th>
<th>Description</th>
<th>Due Date</th>
<th>Action</th>
</tr>
</thead>
<tbody>
#TasksHTMLTable#
</tbody>
</table>
<p>Thank you.</p>
</body>
Last edit: 7 months 2 weeks ago by Onkar2002.
Please Log in or Create an account to join the conversation.
- Peter.Jonson
-
- Offline
- Administrator
- Peter.Jonson
-
- Offline
- Administrator
7 months 2 weeks ago #25865
by Peter.Jonson
Peter Jonson
ETL Developer
Replied by Peter.Jonson on topic Nested Database Loop executes once and stops
Hi Onkar.
To make it work you have to use loop end.
To make it work you have to use loop end.
Peter Jonson
ETL Developer
Please Log in or Create an account to join the conversation.