Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Nested For Each

Posted on 2010-11-10
4
766 Views
Last Modified: 2013-11-10
Hi,

I'm not sure if is possible in SSIS but I have 2 DataSet's and I would like to do a nested loop.

Therefore:

For Each Row in DataSet1 (loop through each header)
          For Each Row in DataSet2 (loop through the details that are equal to the header)

To help you understand DataSet1 is the Headers and DataSet2 is the Details.  Additionally the Header Rows are each numbered and the Detail Rows have the unique header row number in them.

I know I can do this in Scripting tasks but I just wondered if there was a proper/elegant way of doing it in SSIS.

Any help greatly appreciated.

Mike
0
Comment
Question by:hydev
4 Comments
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 250 total points
ID: 34101329
Why not create 1 dataset with all the rows?


And you can loop just once.


hope it helps.


Cheers
SELECT * 
FROM Headers AS H
LEFT JOIN Details AS D
ON D.headerID = H.ID

Open in new window

0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 250 total points
ID: 34101505
If you are not using a SQL data source and are in fact importing from files etc then you have the following options.

1)   Yes you can do a nested loop although you will have to do it using variables. Also this will be row based and could take longer if there a many rows.
2)   You may be able to get the results you are after by doing this in a data flow task using the merge or merge join transformations.

It really depends on what you want to do. For example you have not specified if you want to perform and action for each rows.

If you could give a bit more detail as far what you want the entire package to do i should be able to give a more precise answer.
0
 
LVL 18

Expert Comment

by:deighton
ID: 34101907
if this is .net datatables and datasets then you can set up and use data relations to return all the child rows for a parent
0
 

Author Closing Comment

by:hydev
ID: 34102324
You have both given me ideas to possible solutions so thanks.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 7 31
SQL Server / Update DB? 22 36
SQL Dump exec output to table 3 21
AD and SQL Server 2016 2 28
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question