Please also have a look in our OnlineHelp for further information.

Sometimes data is stored in master and detail tables. This example shows, how to query data from the master table, loop over the result and fetch the details for each row. We’re fetching each material row (master table) before the Date X, then select the item table (storage location data) and insert this data into an existing destination table.

First 2 variables are created in the SSIS-Project that we need later on.

The change date (LAEDA) is located in the header table “MARA”. The item data in the Table “MARD”. A For each Loop Container is used to process data from both tables.

The first data flow gets all material numbers, which are changed before 2003-01-01.

The result of the table MARA is stored in a Record set Destination. A so called In-Memory Dataset. In this dataset you can store material numbers in a table within a variable.

A For each Loop Container is needed in the Control Flow to loop over the material numbers which are in the dataset.

The For each Loop Container is configured as shown below:

…so the material number is written in the variable @MATNR for every row of the loop.

In the For each Loop Container, the following data flow will be executed:

The Xtract IS Table component contains a where clause where the variable @MATNR is filled by the For each Loop Container. In every loop, the material number which was filtered in the header data, can be found there.

If the package is started, the first data flow fills the dataset (which is stored in the variable “@MARA”) with the material numbers of the MARA table. This variable is used by the For each Container to store the actual material number in the variable @MATNR in every loop. The @MATNR variable is used in the where clause inside the data flow within the container to get the related rows from the table MARD and write them into the destination table.

Successful executed package: