Exercise: List out the names of all Excel workbooks on a USB disk

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 Wise Owl course listed below!

Category ==> SSIS Integration Services  (18 exercises)
Topic ==> Foreach loops  (3 exercises)
Level ==> Relatively easy
Course ==> Integration Services

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.

Create a new, empty text file in some folder called Excel workbooks.txt.

The aim of this exercise is to add one row to this file for each Excel workbook on your USB disk.  To start, create a package called List workbooks.

Create a variable to hold the name of each file in turn:

Variable to hold file name

If you're looping over files, you need to hold the name of each in a variable.

 

Now create a Foreach Loop task to loop over all files on the USB stick with path *.xlsx, including any subfolders:

Foreach Loop

You could run this package, but it wouldn't do anything very exciting yet!

 

To write out the name of each workbook to a text file, we need to call some script through a script task:

Add a script task.

Add a script task, and pass the WorkbookName variable to it.

 

Here's what the script could look like:

// create a stream to write to text file (appending)

System.IO.TextWriter sw = new System.IO.StreamWriter(

"E:\\Excel workbooks.txt",true);

 

// write out the name of this workbook

sw.WriteLine(Dts.Variables["WorkbookName"].Value);

 

// close the stream

sw.Close();

If you want to know more about how this works, please do ask your trainer.

Run your package to check it works.  You should see a list of all of the Excel workbooks on your USB disk:

The list of files

Your list will have more files in than this, and the folder paths will be different.

If you still have time, try adding a File System task to the start of the package to delete this text file before recreating it:

The fiie system task.

You'll need a file connection manager to to get this to work.

 

Close your package down!

This page has 0 threads Add post