EXERCISE TOPIC▼
- Access exercises (91)
- C# exercises (79)
- Excel exercises (278)
- Power Apps exercises (13)
- Power Automate exercises (18)
- Power BI exercises (139)
- Power Platform exercises (157)
- Python exercises (28)
- Report Builder exercises (141)
- SQL exercises (198)
- SSAS exercises (51)
- SSIS exercises (46)
- SSRS exercises (99)
- VBA exercises (85)
- Visual Basic exercises (46)
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 | Previous versions exercise | Create one text file for each mentor's contestants
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 ==> | Previous versions (18 exercises) |
| Level ==> | Harder than average |
| 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.
This is a much harder exercise (because less help is given)! The aim of the exercise is to create one text file for each mentor:
|
|
| The files created | What Cheryl Cole's file will be |
To start, run the SQL script in the above folder to create a blank table called tblContestant (first delete any version you might already have of this table).
Create a package called Divide contestants by mentor, and within this a data flow task to import rows from the Excel workbook in the above folder into this table. You should now have a table containing 109 contestants.
The start of your table. Your task now is to create one text file for each mentor's contestants.
Create the final package (help is given below on the task shown selected):
Delete any old records from tblContestant, import new ones from the Excel workbook in the folder above, create an Execute SQL task to run the command SELECT DISTINCT Mentor FROM tblContestant and put the results in an object variable, then finally loop over the rows of this object variable, creating a text file for each mentor (see below for more on how this should work).
The data flow for the selected task above should look something like this:
Read in the contestants for this mentor, then export them to a text file with the right name.
To read in the contestants, use a SQL command like this:
SELECT Contestant, Position FROM tblContestant WHERE Mentor= ?
You'll need to map parameters as follows (assuming you've called your mentor name variable Mentor):
SSIS will substitute in the mentor's name for the first (and only) parameter value.
The destination for this data flow should be a text file with a connection manager:
Add a flat file connection manager, and configure it as shown below.
Here's how to set the connection string for this connection manager:
Add an expression which sets the connection string to be something like the expression shown (use double \ for file path delimiters).
If you need any help with any of this - ask your trainer!