EXERCISE TOPIC▼
SSIS EXERCISES▼
SSIS INTEGRATION SERVICES EXERCISES▼
- Data flow tasks (4)
- Basic data transforms (1)
- Data conversion transforms (2)
- Conditional split transforms (5)
- Lookup transforms (4)
- Looping over files (3)
- Looping over rows (2)
- Merge joins (1)
- Previous versions (18)
- Script tasks (1)
- Variables in script (1)
- Script components (2)
- Accessing file attributes (2)
SSIS Integration Services | Looping over rows exercise | Loop over file names in object variable rows to import them
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
| Software ==> | SSIS Integration Services (46 exercises) |
| Version ==> | SSIS 2012 and later |
| Topic ==> | Looping over rows (2 exercises) |
| Level ==> | Average difficulty |
| Subject ==> | SSIS training |
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
In SQL Server Management Studio, open the SQL file in the above folder and execute it to create this table:
The first table is the one you need to populate. The second table specifies which of the four Excel files in the above folder you want to import. You're welcome to change the letters if you like (but only use A, B, C or D).
Create a new package called Exploitative TV, and within this create two variables:
| Variable | What it will hold |
|---|---|
| Letters | The recordset returned from the table BushtuckerImports. |
| Letter | Each letter in turn as you loop through this recordset. |
Create two Execute SQL tasks - one to get rid of any old rows in the BushtuckerData table, and the other to read the letters in the BushtuckerImports table into your object variable - as well as a loop task as explained below:
The bottom task should loop over the rows in the Letters object variable, reading the import letter for each into your Letter variable.
Within the loop, create a data flow task to read a fixed Excel workbook's data (choose trial A, for example) into your bushtucker data table:
What the data flow task could look like.
Now make the Excel file path for your Excel workbook connection string into an expression, so that when you run your package it imports data only for the letters you've specified:
What you'd get for letters A, B and D (although not necessarily in this order).
Close your package down!