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 | Previous versions 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.
You can learn how to do this exercise on the relevant Wise Owl classroom training course (sadly for the moment only in the UK).
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:
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:
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, 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(
// write out the name of this workbook
// close the stream
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:
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:
You'll need a file connection manager to to get this to work.
Close your package down!