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.

The answer to the exercise will be included and explained if you attend the course listed below!

Category ==> SSIS Integration Services  (40 exercises)
Topic ==> Looping over rows  (2 exercises)
Level ==> Average difficulty
Course ==> Introduction to SSIS
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

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:

Two tables created

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 Execute SQL tasks and loop

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:

Data flow task

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:

The final table

What you'd get for letters A, B and D (although not necessarily in this order).

 

Close your package down! 

This page has 0 threads Add post