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.

The answer to the exercise will be included and explained if you attend the relevant Wise Owl course (sadly, only in the UK for now).

Software ==> SSIS Integration Services  (40 exercises)
Version ==> SSIS 2012 and later
Topic ==> Previous versions  (18 exercises)
Level ==> Harder than average
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.

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:

List of mentor files List of mentors
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 contestants table

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 overleaf on the task shown selected):

The final package

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:

Data flow to select contestants

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):

Parameter for mentor name

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:

Flat file 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:

The connection string property

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!

This page has 0 threads Add post