Creating New Flat Files in Integration Services

SSIS works pretty well, on the whole, but it always throws me when it comes to creating flat files (writiing to existing flat files is fine, because the column headers are already set up).  So here's a quick run through to provide help for anyone who needs it!

The Problem

We want to export a list of X Factor mentors to a flat file:

If you want, you can get a copy of this file here.  Here's what the answer will look like:

The text file produced by the package.

One Solution

This isn't the only solution, but it is one which will work.  First create a package containing a data flow task as follows:

The data flow task should contain an Excel source.

Now add a Flat File destination from the Other Destinations category (using the Destination Assistant doesn't seem to work):

Double-click on this destination to add it to your data flow task.

Now connect the source to the destination:

Feed the output from the Excel workbook into this new flat file.

Double-click on this destination to configure it - it doesn't yet have a connection:

There is no flat file connection yet, not surprisingly.

Click on the New... button on the right to add a connection:

Click on this button to create a new flat file connection.

Choose the sort of flat file you want to create:

We're going to go for a simple comma-delimited flat file.

Give your connection a name (here we've called ours Link to test file) and also - more importantly - choose what you're going to call the new flat file you're creating (here we're calling it testfile.text):

You can also click on the Browse button (not shown in this diagram) to change the folder in which the file will be created.

Make sure that you choose to put column headings at the top of your flat file:

You should normally tick this box to create a first row giving the column headings.

When you click on the Columns tab, you should see the headings set up for you:

The columns MentorId and MentorName have been created for you.

Back in the Flat File editor, you can now map your columns:

Click on this tab to map your columns in the usual way.

Everything should now work normally!