This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SSIS Integration Services (28 exercises)
Topic ==>	Looping over rows (2 exercises)
Level ==>	Average difficulty
Subject ==>	SSIS training

Read rows from a SQL Server table into an object variable, and use an ADO loop to import one workbook per row

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.

Before you can do this exercise, you'll need to download and unzip this file .

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: