asked on

How to create an SSIS package that loops through multiple queries/views for multiple people

I have to create a csv file nightly that captures various data for our patients based on the visit type. Because the queries do not easily combine into one, I have 4 basic queries. I need to run the first one and use that unique patient identifier for subsequent queries but also save the output of each of those to a file or multiple files organized by patient.
Once it finishes the first patient it needs to loop through the remainder of patients that had that visit type for that day and go through all 4 queries as well.
If there were a logical way to combine these queries that would be great but I'm not really sure there is.
I'm attaching a spreadsheet with the tables involved and the queries.
I'm not sure this can be done so any guidance in getting it to a point where it can, even if multiple steps or SSIS packages etc. is fine. Thanks
Queries_tables_rev2.xlsx

ValentinoV

One of your options to achieve this is by using the Foreach Loop container in combination with an ADO Dataset. Your first query populates the dataset which is then looped over to process each patient identifier and export their data to file.

Details on how to set this up: Looping Through a Result Set with the ForEach Loop

ASKER CERTIFIED SOLUTION

Arifhusen Ansari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

biotec

ASKER

I will give this a try. Thanks very much for the explanation.